Home / Questions / 553132
Accepted
amblador
Asked: 2022-08-23 11:09:12 +0800 CST 2022-08-23 11:09:12 +0800 CST

Filter records in a jsonb array in postgres

  • 772

I have a Postgres table with a jsonb field where an array of json is stored:

PURCHASE TABLE

go buyer items_purchased
1 Bob [{"name": "Dell 123 Laptop Computer", "price": 1300, "productid": 1}, {"name": "Mechanical Keyboard", "price": 120, "productid": 2}]
two carol [{"name": "Virtual Keyboard", "price": 150,"productid": 3}, {"name": "Dell 123 Laptop Computer", "price": 1300,"productid": 1}, { "name": "LG Ultrawide Monitor", "price": 190, "productid": 8}]
3 ted [{"name": "Ergonomic Keyboard", "price": 90, "productid": 6}, {"name": "Dell 789 Desktop Computer", "price": 120, "productid": 7}]
4 Alice [{"name": "Dell 789 Desktop Computer", "price": 120, "productid": 7}, {"name": "Mechanical Keyboard", "price": 120, "productid": 2}]

Given for example a productid , I want to get all the records where that item was purchased. For example, for productid = 1 I look for this output:

go buyer items_purchased
1 Bob [{"name": "Dell 123 Laptop Computer", "price": 1300, "productid": 1}]
two carol [{"name": "Dell 123 Laptop Computer", "price": 1300, "productid": 1}]

What I have tried is the following:

select * from purchases where (items_purchased->>'productid')::integer = 1;

but it returns an empty table.

i also tried

select * from purchases where
items_purchased @> ANY (ARRAY ['{"productid":1}']::jsonb[]);

getting again an empty table.

I also tried various combinations of the following query

select * from purchases, jsonb_array_elements(items_purchased) with ordinality arr(item_object, position) where items_purchased->>'productid'::integer = 1;

but I always get syntax or explicit data conversion errors.

postgresql

2 Answers

  1. try usingCAST

    select * 
from purchases 
where CAST(items_purchased->>'productid' AS INTEGER) = 1
    • 2
  2. Best Answer

    I finally found a way to do it with the following query:

    SELECT o.obj, p.id, p.purchaser
FROM purchases p
CROSS JOIN LATERAL jsonb_array_elements(p.items_purchased) o(obj)
WHERE o.obj ->> 'productid' = '1';

    This is the output:

    object go buyer
    {"name": "Dell 123 Laptop Computer", "price": 1300, "productid": 1} 1 Bob
    {"name": "Dell 123 Laptop Computer", "price": 1300, "productid": 1} two carol

    (2 rows)

    • 2