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?

Answer 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.