PostgreSQL select specific items from JSONB array

Question

I have a table like this:

orders

order_number   products
1234           [{"upc":2434354, "title":"Widget"}]
4321           [{"upc":6434556, "title":"Shorts"}, {"upc":54346, title: "Shirt"}]

I want to generate an output like this

[
{order_number: 1234, upc: 2434354},
{order_number: 4321, upc: 6434556}
]

As you can see I pull out the UPC from the array (targeting the first element in the array only).

Note that products if of type jsonb

I tried this:

SELECT jsonb_array_elements(products)[0]->>upc FROM orders ORDER BY created DESC LIMIT 10

But it gives a syntax error, I'm not sure exactly how to do array traversal in PostgreSQL

Also there is the possibility that products may be an empty array []

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

demo:db<>fiddle

SELECT
    jsonb_agg(
         jsonb_build_object(
             'order_number', order_number, 
             'upc', products -> 0 -> 'upc'
         )
    )
FROM
    orders

products -> 0 -> 'upc' gets the upc value of the first array element
jsonb_build_object() builds one JSON object per record containing the order_number and upc value
jsonb_agg() aggregates these JSON objects into one JSON array

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 23, 2019 at 15:52

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL select specific items from JSONB array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related