Simplify a jsonb request

by

The question:

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"}

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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[*] ? (@.id == "1234")') as actions
from layouts
WHERE id = 124350001
  AND "deletedAt" IS NULL;

Online example


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

Leave a Comment