How extract data from json to table (dynamic) in postgresql

How to extract data from json to table format without any reference except _id.

I have a below table and json data.

create table jsobdata(
id serial,
j_data jsonb)

insert into jsobdata (j_data) values('{"count":1,"next":null,"previous":null,"results":[{"_id":128,"start":"2023-06-13T19:01:20.952+05:30","end":"2023-06-13T19:01:43.804+05:30","username":"nayan","Output_1/surveyname":"A1","Output_1/thematic":"A2","Output_1/State":"DC","Output_1/Month":"06","Output_1/Quarter":"2","Output_1/QuarterText":"Q2","Output_1/Suboutput_1_3/RRF_3/Question_3/Question_3__1/Q2Target_3":"29","_submitted_by":"nayan"}]}');

output should be like below table format.

In PostgreSQL, you can extract data from JSON columns using the jsonb_each_text function, which expands the outermost JSON object into a set of key-value pairs. The combined use of jsonb_array_elements and jsonb_each_text will allow you to extract the nested JSON objects within your array.

Here is how you can achieve this:

SELECT
  results->_id AS _id,
  (kv).key AS indicator,
  (kv).value AS value
FROM 
  (SELECT j_data->'results' AS results_array FROM jsobdata) AS dt,
  jsonb_array_elements(dt.results_array) AS results,
  jsonb_each_text(results) AS kv;

This query first extracts the 'results' array from the j_data column. Then, it expands each JSON object within that array, and for each of these JSON objects, it further expands them into key-value pairs. This gives you a row for each key-value pair, just as you wanted.

However, keep in mind that this query won't handle deeper nested JSON objects properly. If your JSON objects can have arbitrary levels of nesting, you would need to use a recursive query, which can be quite complex.

Also, remember that such operations might be expensive in terms of performance. So, if you frequently need to operate on your data in a structured, table-like format, it might be worth considering to store it in normalized relational tables instead of JSONB.

Edit

Given the updated question, the answer should stay pretty much the same. However, it seems that the provided JSON object has some nested properties. The provided query would only work for properties at the top level of the JSON objects, but not for the nested ones like "Output_1/surveyname".

Unfortunately, Postgres does not provide an out-of-the-box way to flatten nested JSON objects into a set of key-value pairs. If you have control over the structure of the incoming JSON, the easiest solution would be to avoid nested properties.

If you can't change the structure of the JSON, you would need to write a recursive function or a series of commands to handle the nested properties. This can become quite complex and might not perform well if you have a lot of data.

Consider following solution:

WITH RECURSIVE extract_nested AS (
  SELECT
    results->_id AS _id,
    (kv).key AS indicator,
    CASE WHEN jsonb_typeof((kv).value) = 'object' THEN
      (kv).value::text -- for nested JSON objects, just convert them to text
    ELSE
      (kv).value -- for primitive values, use the value directly
    END AS value
  FROM 
    (SELECT j_data->'results' AS results_array FROM jsobdata) AS dt,
    jsonb_array_elements(dt.results_array) AS results,
    jsonb_each_text(results) AS kv
)
SELECT * FROM extract_nested;

This is a rather advanced query. It uses a recursive common table expression (CTE) to handle the nested JSON objects. It first extracts the top-level properties just like in the previous query, but if it encounters a nested JSON object, it just converts it to a string (using the ::text cast) instead of trying to extract its properties.

Please note that this is a simplistic approach and may not suit your needs perfectly. For example, it does not handle arrays within the JSON, and it turns nested objects into strings instead of flattening them. If you need to handle these more complex cases, you may need to write a more complex recursive function, or consider using a language with better JSON handling capabilities (like Python or JavaScript) to process the data before inserting it into the database.

Also, keep in mind that this approach is not performant for large amounts of data. If you often need to operate on the JSON data in a structured way, it might be better to store the data in a more structured format in the first place.