Is it possible to get json values as list from array of json in Postgres?

Question

I use Postgresql 10 and suppose, that I have the following table:

CREATE TABLE test_table(id int8 NOT NULL, data jsonb);

And inserted such values:

insert into test_table(id, data)
values (1, '{"external": [{"internal_1": "value_1", "internal_2": "some_value"}]}'),
       (2, '{"external": [{"internal_1": "value_2", "internal_2": "another_value"}]}'),
       (3, '{"external": [{"internal_1": "value_1", "internal_2": "other_value"}]}');

The table after all:

id|                                data                                     |
--|-------------------------------------------------------------------------|
1 |{"external": [{"internal_1": "value_1", "internal_2": "some_value"}]}    |
2 |{"external": [{"internal_1": "value_2", "internal_2": "another_value"}]} |
3 |{"external": [{"internal_1": "value_1", "internal_2": "other_value"}]}   |

The problem is that I need to have all internal values as an array

I tried the following query:

select data -> 'external'
from test_table;

and the result is:

?column?                                                         |
-----------------------------------------------------------------|
[{"internal_1": "value_1", "internal_2": "some_value"}]          |
[{"internal_1": "value_2", "internal_2": "another_value"}]       |
[{"internal_1": "value_1", "internal_2": "other_value"}]         |

But finally I need to have internal values as text array for each table row. Something like that:

?column?                       |
-------------------------------|
[value_1, some_value]          |
[value_2, another_value]       |
[value_1, other_value]         |

Is it possible to write such query using postgres json functions and operators?

UPD!! Number of internal keys could be different (internal_1, ..., internal_n) and we don’t know in advance names of keys (in my case they are: internal_1 and internal_2)

What do you want the result to look like when there are multiple objects in the arrays? Do you expect the objects to have keys that are not internal_… (and if yes, what to do with them)? — Bergi
– Bergi, Commented May 30, 2020 at 16:07

fgasparini · Accepted Answer · 2020-05-30 16:27:59Z

1

The following query should to what you want:

select json_build_array(data#>>'{external,0, internal_1}', data#>>'{external,0, internal_2}')
from test_table;

The idea is to use the json_build_array function to create an array and them you need to select each element that will be part of it, a convenient way to do it is by using the #>'{json_path}' so you select the elements using a json path.

Source: https://www.postgresql.org/docs/10/functions-json.html

SQLFiddle to test it: http://sqlfiddle.com/#!17/5c4d2/19

answered May 30, 2020 at 16:27

fgasparini

1,3289 silver badges10 bronze badges

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

1 Comment

Kim Over a year ago

Thank you for your answer! My fault, I forgot to say that number of internal keys could be different (internal_1, ..., internal_n) and we don’t know in advance names of keys (in my case they are: internal_1 and internal_2). Tomorrow I’ll update my question with new terms.

Barbaros Özhan · Accepted Answer · 2020-05-31 06:05:16Z

0

One approach would be splitting the elements through going deep into each key individually in an explicit manner, and then using jsonb_build_array() function :

SELECT jsonb_build_array(
       (data ->> 'external')::jsonb -> 0 ->> 'internal_1',
       (data ->> 'external')::jsonb -> 0 ->> 'internal_2')
  FROM test_table

Or more dynamically (for the cases of having more or less internal keys) jsonb_each_text() might be used within a CROSS JOIN query to extract each key and then jsonb_agg() might be applied to combine them within arrays :

SELECT id, 
       jsonb_agg(js.value)
  FROM test_table
 CROSS JOIN jsonb_each_text( ((data ->> 'external')::jsonb -> 0)::jsonb) AS js
 GROUP BY id
 ORDER BY id

Demo

edited May 31, 2020 at 6:05

answered May 30, 2020 at 16:35

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

Kim Over a year ago

Thank you for your answer! My fault, I forgot to say that number of internal keys could be different (internal_1, ..., internal_n) and we don’t know in advance names of keys (in my case they are: internal_1 and internal_2). Tomorrow I’ll update my question with new terms.

Kim Over a year ago

Thank you very much, @barbaros-Özhan! That works perfectly!

Collectives™ on Stack Overflow

Is it possible to get json values as list from array of json in Postgres?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related