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.

1. Table (product)

---

id(int4)|         data(jsonb)        |       category__id

---

2. 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
    }
 ]
}

3. My query

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

4. Expected output

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

Answer 1

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