PostgreSQL SELECT only values inside jsonb data

Question

I have a postgresql data with values (it is jsonb type column):

SELECT data FROM orders;

   [
    {
        "food_id": "1",
        "table": "A12",
    },
    {
        "food_id": "2",
        "table": "A14",
    }
   ]

I can easily SELECT by providing data as it is, but how to convert it into simplified ?

My expected result:

SELECT ??? as food_tables FROM orders;

["A12", "A14"]

I personally still did not understand how jsonb_array_elements() works.

Thanks!

Answer 1

You could perform a lateral cross join with the unnested array elements and extract the attributes:

SELECT jsonb_agg(d.elem -> 'table')
FROM orders
   CROSS JOIN LATERAL jsonb_array_elements(orders.data) AS d(elem)
GROUP BY orders.id;

Use array_agg instead of jsonb_agg if you want a PostgreSQL array.

It is a mistake to model tabular data as a JSON array. Change your data model so that each array element becomes a row in a database table.