Clear ORM
Search…
JSONB
Clear offers JSONB functions through Clear::SQL::JSONB helper and the Expression Engine.
JSONB is a great No-SQL mapping under PostgreSQL. It helps you to store value and documents which otherwise would be difficult
Let's imaging a table events where you store the events of differents suppliers:

Postgres limitation and Clear's answer

The main limitation of JSONB is the "simple" syntax is not indexable. For example:
1
SELECT * FROM events WHERE payload->'source'->>'name' = 'Asana'
Copied!
The code above will not use any indexes and will do a sequencial scan over your table.
However, using the @> operator and a gin index on your column will improve drastically the performances:
1
SELECT * FROM events WHERE payload @> '{"source": {"name": "Asana"}}'
Copied!
Obviously, the second syntax is more complex and error prone. Clear offers leverage and simplicity:
1
Event.query.where{ payload.jsonb("source.name") == "asana" }
2
# SELECT * FROM events WHERE payload @> '{"source": {"name": "Asana"}}'
Copied!

Expression Engine

jsonb

calling node.jsonb(key) on expression node will resolve to:
  • node->'key_elm1'->'key_elm...n'
Using equality testing between a jsonb path and a literal will use the indexable notation @> :
1
where{ data.jsonb('a.b.c') == 1 }
2
#output:
3
# data @> '{"a":{"b":{"c":1}}}'
Copied!
In the case the operation is not indexable (e.g. the value is variable, operator is not equality...), Clear will automatically switch back to the arrow -> notation:
1
where{ data.jsonb('a.b.c') == raw("NOW()") }
2
# output:
3
# data->'a'->'b'->'c' = NOW()
Copied!
Casting
You can cast the element using cast after your expression:
1
where{ data.jsonb("a.b").cast("text") == "o" }
2
# output:
3
# data->'a'->'b'::text == 'o'
Copied!
Note: If you cast the jsonb, clear will never use @> operator

From path to arrow notation

1
Clear::SQL::JSONB.jsonb_resolve("data", "a.b.c", "text")
2
# output:
3
# data->'a'->'b'->'c'::text
Copied!

Use outside Expression Engine (@> operator)

1
Clear::SQL::JSONB.jsonb_eq(data, "a.b.c", "value")
2
#output:
3
# data @> {"a":{"b":{"c":"value"}}}
Copied!
Last modified 1yr ago