PostgreSQL Question posted in
The official documentation can be found here.

How do I search within a PostgreSQL jsonb array?

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?

Tags:

2

Answers


  1. 0

    You can use the current query

    SELECT arr.position,arr.item_object
 FROM 
customers,
 jsonb_array_elements( "orders") with ordinality arr(item_object, position) 
    where 
     and arr.item_object->>'catogory' = 'tools'
     and arr.item_object->>'item_num' = '15'
    ;

    where the jsonb_array_elements will create a new table with the name arr that has the elements of the array as rows, and then you can query them using the new table name arr

    enter image description here

    One important note, you need to make sure that all your column have arrays not object, or it will raise an error.
    enter image description here

    Login or Signup to reply.
  2. 0

    You can use a lateral join with the jsonb_array_elements function:

    SELECT
    id,
    order->'tracking_num',
FROM
    customers,
    jsonb_array_elements(jdoc->'orders') order
WHERE
    order->>'item_num' = '15'
    AND order->>'category' = 'tools'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search