Simplify a jsonb request

All we need is an easy explanation of the problem, so here it is.

I am a newbie in request involving jsonb.

Can this one be improved? collections is a huge jsonb field and maybe one cross join is enough.

SELECT actions
FROM layouts
         CROSS JOIN jsonb_array_elements(elements) AS element
         CROSS JOIN jsonb_array_elements(element.value->'sub'->'actions') as actions
WHERE id = 124350001
  AND actions->>'id' = '1234'
  AND "deletedAt" IS NULL;

Here is an example of the "elements" field value:

  "sub": { "actions": [{"id":"1234", "name": "one"},{"id":"45678", name: 'two'}] }

The request should return an action, for example:

{"id":"1234", "name": "one"}

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can use a JSON path query, to extract that element from the column’s value:

select jsonb_path_query_first(elements, '$.sub.actions[*] ? ( == "1234")') as actions
from layouts
WHERE id = 124350001
  AND "deletedAt" IS NULL;

Online example

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply