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!

"I have a postgresql data with values" - multiple values (rows)? Or just one? — Bergi
– Bergi, Commented Jun 8, 2021 at 0:34
"I personally still did not understand how jsonb_array_elements() works." - it seems you already know the right approach. Can you be more specific about what you didn't understand, and show us how you tried to use it? — Bergi
– Bergi, Commented Jun 8, 2021 at 0:36
@Bergi multiple. I just dont know how to write syntax for the SELECT — mokalovesoulmate
– mokalovesoulmate, Commented Jun 8, 2021 at 2:45

user330315 · Accepted Answer · 2021-06-08 05:30:34Z

1

If you are using Postgres 12 or later, you can use jsonb_path_query_array()

select jsonb_path_query_array(data, '$[*].table') as food_tables
from orders

answered Jun 8, 2021 at 5:30

user330315

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

2 Comments

mokalovesoulmate Over a year ago

Sorry I did not specify my PostgreSQL version. Mine is using 9.6

user330315 Over a year ago

@mokalovesoulmate: then you will need to use Laurenz's answer

Laurenz Albe · Accepted Answer · 2021-06-08 06:35:09Z

0

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.

edited Jun 8, 2021 at 6:35

answered Jun 8, 2021 at 0:45

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

4 Comments

mokalovesoulmate Over a year ago

Hi Laurenz, thanks for the answer. I cannot update the current data model since it is an existing old app. I tried your query but that is not what I needed. I needed the A12 and A14 in the same row. I've updated the question into "expected result".

mokalovesoulmate Over a year ago

Hi Laurenz, I see you updated your answer. Its almost correct! Unfortunately it also merges all other rows from same table into single row. @a_horse_with_no_name answer is the one.

Laurenz Albe Over a year ago

You didn't specify what you want.

mokalovesoulmate Over a year ago

Thank you and sorry @Laurenz! I was okay to upgrade to pg12 but after seeing your updated answer, I can stay with pg9.6. Also great help with @a_horse_with_no_name.

Collectives™ on Stack Overflow

PostgreSQL SELECT only values inside jsonb data

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related