Clear ORM
  • Welcome to Clear
  • Introduction
    • Setup
  • Model
    • Defining your model
      • Describing your columns
      • Primary Keys
      • Converters
    • Associations
      • belongs_to
      • has_many
      • has_many through
      • has_one
    • Lifecycle
      • Persistence
      • Validations
      • Triggers
    • Batchs operations
      • Bulk update
      • Bulk insert & delete
    • Transactions & Save Points
      • Transaction & Savepoints
      • Connection pool
    • Locks
  • Querying
    • The collection object
      • Filter the query
        • Filter the query – The Expression Engine
        • Find, First, Last, Offset, Limit
        • Aggregation
        • Ordering & Group by
      • Fetching the query
        • Each and Fetch
        • Cursored fetching
        • Model extra attributes
      • Joins
      • Eager Loading
      • Window and CTE
      • Scopes
    • Writing low-level SQL
      • Select Clause
      • Insert Clause
      • Delete Clause
  • Migrations
    • Manage migrations
    • Call migration script
    • Migration CLI
  • Additional and advanced features
    • JSONB
    • Symbol vs String
    • Enums
    • BCrypt
    • Full Text Search
    • Handling multi-connection
  • Other resources
    • API Documentation
    • Inline documentation
    • Github repository
    • Credits
    • Benchmark
Powered by GitBook
On this page
  • Postgres limitation and Clear's answer
  • Expression Engine
  • jsonb
  • From path to arrow notation
  • Use outside Expression Engine (@> operator)
  1. Additional and advanced features

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:

  SELECT * FROM events WHERE payload->'source'->>'name' = 'Asana'

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:

  SELECT * FROM events WHERE payload @> '{"source": {"name": "Asana"}}'

Obviously, the second syntax is more complex and error prone. Clear offers leverage and simplicity:

  Event.query.where{ payload.jsonb("source.name") == "asana" }
  #  SELECT * FROM events WHERE payload @> '{"source": {"name": "Asana"}}'

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 @> :

where{ data.jsonb('a.b.c') == 1 }
#output:
# data @> '{"a":{"b":{"c":1}}}'

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:

where{ data.jsonb('a.b.c') == raw("NOW()") }
# output:
# data->'a'->'b'->'c' = NOW()

Casting

You can cast the element using cast after your expression:

where{ data.jsonb("a.b").cast("text") == "o" }
# output:
# data->'a'->'b'::text == 'o'

Note: If you cast the jsonb, clear will never use @> operator

From path to arrow notation

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

Use outside Expression Engine (@> operator)

Clear::SQL::JSONB.jsonb_eq(data, "a.b.c", "value")
#output:
# data @> {"a":{"b":{"c":"value"}}}
PreviousMigration CLINextSymbol vs String

Last updated 5 years ago