How do I get all column values in case of nested json values in column for sql?

Question

Here is a sample of the data that I am working with:

id    |  col1    |  col2
1      |  Name1 | {'spec_details': {'spec_values': [{'name':'A','value':2}, {'name': 'B', 'value': 5}, {'name': 'C', 'value': 6}], 'spec_id': 'ASVSDAS'}, 'channel': 'channel1'}
2    | Name2 | {'spec_details': {'spec_values': [{'name':'A','value':9}, {'name': 'B', 'value': 1}, {'name': 'D', 'value': 8}], 'spec_id': 'QWSAASS'}, 'channel': 'channel1'}

In the above case, I want to convert the specific spec_values present within col2 into seperate columns. So, the output I am looking at is:

id   |  col1  |   A  |  B  |  C  |  D | spec_id
1    | Name1  |   2  |  5  |  6  |    | ASVSDAS
2    | Name2  |   9  |  1  |     |  8 | QWSAASS

How can I do this? I know I can get the values of spec_id by using ->> So, it becomes col2->>'spec_id' for getting spec_id values. In case of spec_values, I know I can get specific values at index as col2->'spec_values'[0], and col2->'spec_values'[1] etc. Further, can get specific name etc as col2->'spec_values'[0]->>'name'

However, am looking to have it as a column instead. Can someone please help?

What should happen if there's multiple spec values with the same name in the array? — Bergi
– Bergi, Commented Feb 22, 2024 at 16:26
There can only be one spec value with a given name in the array. However, the number of spec values in the array is not fixed, and vary from 0 to 100 depending upon a given row — Phoenix
– Phoenix, Commented Feb 22, 2024 at 16:30
Well, bad data structure then, you should've used an object for that :-) I'll write up an answer — Bergi
– Bergi, Commented Feb 22, 2024 at 16:34
Thanks a lot. I did some iterations and was able to get all possible unique values for the column: ``` SELECT DISTINCT vals->>'name' FROM ( SELECT DISTINCT JSONB_ARRAY_ELEMENTS(col2->'spec_details'->'spec_values') AS vals FROM table ) AS modified_table ``` I was thinking I could add a WHERE clause here by having it on name and print its related value in the select query. However, since I used distinct to get all unique name values, it is difficult to convert it. Checking using WITH if can modify the sql by having cols. — Phoenix
– Phoenix, Commented Feb 22, 2024 at 16:48

Bergi · Accepted Answer · 2024-02-22 16:52:29Z

1

The easiest approach would probably be to call jsonb_path_query_first multiple times with the respective jsonpath selector. If there might be duplicates, change it to jsonb_path_query_array. The results will have jsonb type, you may want to convert them to ints.

SELECT
   id,
   col1,
   jsonb_path_query_first(col2, 'strict $.spec_details.spec_values[*] ?(@.name == "A") .value') AS a,
   jsonb_path_query_first(col2, 'strict $.spec_details.spec_values[*] ?(@.name == "B") .value') AS b,
   jsonb_path_query_first(col2, 'strict $.spec_details.spec_values[*] ?(@.name == "C") .value') AS c,
   jsonb_path_query_first(col2, 'strict $.spec_details.spec_values[*] ?(@.name == "D") .value') AS d,
   jsonb_path_query_first(col2, 'strict $.spec_details.spec_id') AS spec_id
FROM api_listingdata;

Alternatively, transform the array of name-value pairs into an object, which will make accessing properties by name much easier. jsonb_object_agg can be used for that:

SELECT
  id,
  col1,
  (
    SELECT jsonb_object_agg(el->>'name', el->'value')
    FROM jsonb_array_elements(col2->'spec_details'->'spec_values') el
  ) AS spec_values,
  col2->'spec_details'->>'spec_id' AS spec_id
FROM api_listingdata;

You can also expand such a jsonb object into individual columns using jsonb_to_record, including the desired type conversion:

SELECT id, col1, "A", "B", "C", "D", spec_id
FROM
  api_listingdata,
  jsonb_to_record(col2->'spec_details') AS spec_details(spec_values jsonb, spec_id text),
  jsonb_to_record(
    SELECT jsonb_object_agg(el->>'name', el->'value')
    FROM jsonb_array_elements(spec_values) el
  ) AS spec_vals("A" int, "B" int, "C" int, "D" int);

In any case, you cannot get "all columns in the json" without knowing which columns those are (and ideally, which types they have). Any SQL query must have a static result type that is known before executing the query. If you don't know the column names, either get the JSON (object) value (as shown in the second snippet) and process it in your application logic, or build the SQL query dynamically after determining which columns there are in a previous query.

edited Feb 22, 2024 at 16:52

answered Feb 22, 2024 at 16:47

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

4 Comments

Phoenix Over a year ago

I was trying to understand how you are using jsonb_object_agg in the above case? And why? Since the value of spec_values is already a json itself

Bergi Over a year ago

But it's a weird [{"name":"A","value":2}, {"name":"B", "value":5}, …] array of tuples not a simple {"A": 2, "B": 5, …} object with easily accessible properties.

Phoenix Over a year ago

Is there a way to loop through column names somehow as a tuple when we are showing the results?

Bergi Over a year ago

No, see the last paragraph. You need to do the looping in your application logic.

Collectives™ on Stack Overflow

How do I get all column values in case of nested json values in column for sql?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related