JSONB BUILDER
Creates JSONB selector & compartor strings for use in postgres jsonb
queries.
npm install jsonb-builder
//// Build a comparison for a where statement//const jsonbComparison = column: 'data' /* "( data -> 'a' -> '33_1' ->> 'a' )::int = 1"*/ //// Get just a selector (for use in columns)//const jsonbSelector = column: 'data' /* "( data -> 'a' -> '33_1' ->> 'a' )"*/ //// Escape with a transform//const jsonbComparison2 = column: 'data' Knex/* "( data -> 'a' -> '33_1' ->> 'a' ) = 'some\'; drop table bobby'"*/ //// Get many//const query = column: 'test' /* [ [ "( test ->> 'foo' -> 'bar' )::int = 1" ] [ "( test ->> 'test' -> 1 )::int < 3" ] ]*/ //// Get class instances for more control//const queries = column: 'data' const comparisons = queries/* [ [ "( data ->> 'something' ) LIKE '%word'" ], [ "( data ->> 'equals' ) = 'value'" ], [ "( data ->> 'equals2' ) = 'value'" ], [ "( data -> 'some' -> 'thing' -> 0 ->> '!deeper!' )::int < 5", "( data -> 'some' -> 'thing' -> 0 ->> '!deeper!' )::int > 2" ] ]*/ // use `asSelector` to exclude the comparison logicconst statements = queries/* [ [ "( data ->> 'something' )" ], [ "( data ->> 'equals' )" ], [ "( data ->> 'equals2' )" ], [ "( data -> 'some' -> 'thing' -> 0 ->> '!deeper!' )" ] ]*/
Caveats
- Be sure to escape your values.
Search comparitors
-
$eq
--= 1
-
$ne
--!= 1
-
$like
--LIKE '%str%'
(%
must already exist in input) -
$notLike
--NOT LIKE '%str%'
-
$iLike
--ILIKE '%str%'
-
$notILike
--NOT ILIKE '%str%'
-
$gt
-->
-
$gte
-->=
-
$lt
--<
-
$lte
--<=
-
$between
--BETWEEN
-
$notBetween
--NOT BETWEEN
-
$not
--NOT
-
$in
--IN
-
$notIn
--NOT IN
-
$overlap
--&&
-
$contains
--@>
-
$contained
--<@
-
$any
--ANY
TODO
- Add more comparitors
- Test all comparitors
- Add more type casting logic