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

```sql
  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:

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

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

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

```ruby
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:

```ruby
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:

```ruby
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

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

## Use outside Expression Engine (`@>` operator)

```ruby
Clear::SQL::JSONB.jsonb_eq(data, "a.b.c", "value")
#output:
# data @> {"a":{"b":{"c":"value"}}}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://clear.gitbook.io/project/additional-and-advanced-features/jsonb.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.