I have a jsonb column that looks like this:
{
"customer_id": "1",
"address": "123 Main St.",
"orders": [{
"order_id": "1",
"catogory": "tools",
"item_num": "15",
"tracking_num": "GDR9F654F"
},{
"order_id": "2",
"catogory": "accessories",
"item_num": "28",
"tracking_num": "PHW3X543T"
}]
}
The "orders" key is an array that I want to search within. I’m trying to find all the tracking numbers for customers who order a particular item.
If I try doing something like this:
SELECT
id,
jdoc->'orders'->'tracking_num',
FROM
customers
WHERE
jdoc->'orders'->'item_num' = '15'
AND jdoc->'orders'->'category' = 'tools'
it doesn’t work because it’s within an array.
How do I make sure that the object in the array matches both conditions in the WHERE clause and returns the particular piece of information from that object?
2
Answers
You can use the current query
where the
jsonb_array_elements
will create a new table with the namearr
that has the elements of the array as rows, and then you can query them using the new table namearr
One important note, you need to make sure that all your column have arrays not object, or it will raise an error.
You can use a lateral join with the
jsonb_array_elements
function: