skip to Main Content

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


  1. 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. 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