CODEWITHSUNDEEP
sqljsonpostgresql
Papkie
Papkie

Reputation: 57

Select items from jsonb array in postgres 12

I'm trying to pull elements from JSONB column. I have table like:

id NUMBER
data JSONB

data structure is:

[{
    "id": "abcd",
    "validTo": "timestamp"
}, ...]

I'm querying that row with SELECT * FROM testtable WHERE data @> '[{"id": "abcd"}]', and it almost works like I want to.

The trouble is data column is huge, like 100k records, so I would like to pull only data elements I'm looking for. For example if I would query for SELECT * FROM testtable WHERE data @> '[{"id": "abcd"}]' OR data @> '[{"id": "abcde"}]' I expect data column to contain only records with id abcd or abcde. Like that:

[
{"id": "abcd"},
{"id": "abcde"}
]

It would be okay if query would return separate entries with single data record.

I have no ideas how to solve it, trying lot options since days.

Upvotes: 0

Views: 2236

Answers (3)

Anonymous
Anonymous

Reputation: 92

Didn't quite get your question.Are you asking that the answer should only contain data column without id column .Then I think this is the query:

Select data from testtable where id="abcd" or id="abcde";

Upvotes: 0

karianpour
karianpour

Reputation: 794

To have separate output for records having multiple matches

with a (id, data) as (
  values
    (1, '[{"id": "abcd", "validTo": 2}, {"id": "abcde", "validTo": 4}]'::jsonb),
    (2, '[{"id": "abcd", "validTo": 3}, {"id": "abc", "validTo": 6}]'::jsonb),
    (3, '[{"id": "abc", "validTo": 5}]'::jsonb)
)
select id, jsonb_array_elements(jsonb_path_query_array(data, '$[*] ? (@.id=="abcd" || @.id=="abcde")'))
from a;

Upvotes: 1

user330315
user330315

Reputation:

You will need to unnest, filter and aggregate back:

select t.id, j.*
from testtable t
  join lateral (
     select jsonb_agg(e.x) as data
     from jsonb_array_elements(t.data) as e(x)
     where e.x @> '{"id": "abcd"}'
        or e.x @> '{"id": "abcde"}'
  ) as j on true

Online example

With Postgres 12 you could use jsonb_path_query_array() as an alternative, but that would require to repeat the conditions:

select t.id,
       jsonb_path_query_array(data, '$[*] ? (@.id == "abcd" || @.id == "abcde")')
from testtable t
where t.data @> '[{"id": "abcd"}]'
   or t.data @> '[{"id": "abcde"}]'

Upvotes: 0

Related Questions