How to query select JsonArray from Jsonb field in Postges

Question

I have an issue would need your help. I would like to query select data from JsonArray by id.

Table (product)

id(int4)|         data(jsonb)        |       category__id

Field data contain JsonArray as below

  {
  "item": [
      {
          "id": 1,
          "name": "hawai",
          "size": {
              "L": 0.5,
              "M": 0.15,
              "S": 0.25
          },
          "price": 10,
          "rating": 10,
      },
      {
          "id": 2,
          "name": "koka kola",
          "size": {
              "L": 0.15,
              "M": 0.25,
              "S": 0.35
          },
          "price": 20,
          "rating": 100
    }
 ]
}

My query

select * from product where data->'item'->>'id'=1

Expected output

enter image description here

but query result show all record which consists on field "data" it doesn't match what I prefer. I want only data where id=1

Can you please **[edit]** your question (by clicking on the [edit] link below it) and add the expected output based on your sample data? It's unclear to me if you want to extract matching array elements or if you want to get the whole row where one array element matches — , Jul 21 '20 at 08:35
@a_horse_with_no_name Sorry I tag wrong version . currently I am using PostgreSQL 12. Related to this issue you know how can I select this by id. Thank — James, Jul 21 '20 at 08:37
@a_horse_with_no_name now I have updated my question also link my expect output result. — James, Jul 21 '20 at 08:43

score 0 · Accepted Answer · answered Jul 21 '20 at 09:41

0

This can be done using jsonb_path_query_array()

select id, jsonb_path_query_array(data, '$.item[*] ? (@.id == 1)'), category_id
from product;

This isn't exactly what your picture shows though: it will not include the "item" key:

[{"id": 1, "name": "hawai", "size": {"L": 0.5, "M": 0.15, "S": 0.25}, "price": 10, "rating": 10}]

answered Jul 21 '20 at 09:41

Thank you:) @a_horse_with_no_name for your answer. – James Jul 21 '20 at 09:50
if no id match then it will return `[]`. – Akhilesh Mishra Jul 21 '20 at 10:04
@AkhileshMishra: yes, what else would you expect? If you write a `where` clause that doesn't match, you don't get rows back either – Jul 21 '20 at 10:05
True. we will not get any row if put it in where clause – Akhilesh Mishra Jul 21 '20 at 10:06

Akhilesh Mishra · Answer 2 · 2020-07-21T10:11:11.743

0

Try this:

  select id, 
         json_build_object('item',array_to_json(array_agg(item))),
         category_id 
from (
select id, jsonb_array_elements(data->'item') as "item", category_id 
from example ) tab
where cast(item->>'id' as int)=1
group by id,category_id

Demo

edited Jul 21 '20 at 10:11

answered Jul 21 '20 at 10:00

Akhilesh Mishra

5,876
3
16
32

How to query select JsonArray from Jsonb field in Postges

2 Answers2