Querying dataset by conditions in nested JSONB document

Question

I have a fairly simple table in PostgreSQL database (v 9.6):

CREATE TABLE foobar (id serial, data jsonb);

Here is an example of JSONB document that is stored in data column:

[{ key: 'foo', value: 100 }, { key: 'bar', value: 5 }, { key: 'baz', value: 10 }]

I am trying to write a select that will return every row where conditions in JSONB document are met and select only specified nested documents; i.e. for every row where foo is greater than X return all nested documents that have baz as key.

So far I've came with a query like this but it does not work — it is returning 0 records.

SELECT id
  FROM foobar
 WHERE (data->>'key' = 'foo' AND (data->>'value')::numeric > 5)
    OR (data->>'key' = 'bar' AND (data->>'value')::numeric < 10)

Also it would be great if anyone knows how to optimize the final query. Thanks!

Answer 1

for every row where foo is greater than X return all nested documents that have baz as key.

Use jsonb_array_elements(data) twice. The first for comparing the value with the key foo and the second to find object with the key baz:

with foobar(id, data) as (
values
(1, 
'[
    { "key": "foo", "value": 100 }, 
    { "key": "bar", "value": 5 }, 
    { "key": "baz", "value": 10 }
]'::jsonb)
)

select id, value_baz
from foobar,
jsonb_array_elements(data) el_foo(value_foo),
jsonb_array_elements(data) el_baz(value_baz)
where value_foo->>'key' = 'foo' and (value_foo->>'value')::numeric > 5
and value_baz->>'key' = 'baz';

 id |          value_baz          
----+-----------------------------
  1 | {"key": "baz", "value": 10}
(1 row)

---

The format of your json column is rather strange. I see no reason to use a json array here. You can store the same information in a simple form like this:

'{ "foo": 100, "bar": 5, "baz": 10 }'

In this case your query might be as simple as:

with foobar(id, data) as (
values
(1, '{ "foo": 100, "bar": 5, "baz": 10 }'::jsonb)
)

select id, data->'baz' as baz
from foobar
where (data->>'foo')::numeric > 5;

 id | baz 
----+-----
  1 | 10
(1 row) 

Answer 2

You can achieve this with:

• unnesting your JSON array into elements (using the jsonb_array_elements function)
• HAVING bool_or(...) to ensure at least one of the elements has foo as key and value greater than 5
• aggregate filtered: only those sub-documents, which has bar as key

---

SELECT     id, jsonb_agg(e) FILTER (WHERE e ->> 'key' = 'bar')
FROM       foobar
CROSS JOIN jsonb_array_elements(data) e
WHERE      data @> '[{"key":"foo"},{"key":"bar"}]'
GROUP BY   id
HAVING     bool_or(e ->> 'key' = 'foo' AND e -> 'value' > '5')

Notes:

• e -> 'value' > '5' will compare the value JSON property with the JSON value 5, which works perfectly, if you use numbers in your JSON documents. If that's not the case (i.e. you also have JSON strings, which you want to handle as numbers), use a CAST, like (e ->> 'value')::numeric > 5
• WHERE data @> '[{"key":"foo"},{"key":"bar"}]' is completely optional. I included here, because that's the most you can squeeze out from a GIN index (on the jsonb column; if you have any). But that will be almost useless, if those keys are on almost every row in your table.
• Unfortunately every solution will require the use of jsonb_array_elements(), which defeats the use cases of JSON indexes. With your current design, a sequential table scan is unavoidable (unless the predicate above can actually filter out a lot of rows).

http://rextester.com/XCPB80482