Select items from jsonb array in postgres 12

Question

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.

This sounds as if a properly normalized one-to-many relationship would be a lot better — user330315
– user330315, Commented May 4, 2020 at 11:35
Yeah, I totally agree, I'm going to write migration soon, but I need it to make hot fix. — Papkie
– Papkie, Commented May 4, 2020 at 14:12

karianpour · Accepted Answer · 2020-05-04 12:05:21Z

1

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;

answered May 4, 2020 at 12:05

karianpour

7947 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 0 · Accepted Answer · 2020-05-04 11:51:23Z

0

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

edited May 4, 2020 at 11:51

answered May 4, 2020 at 11:41

user330315

Comments

Anonymous · Accepted Answer · 2020-06-12 14:11:14Z

0

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

edited Jun 12, 2020 at 14:11

answered May 4, 2020 at 11:46

Anonymous

923 silver badges12 bronze badges

Collectives™ on Stack Overflow

Select items from jsonb array in postgres 12

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related