Convert the given JSON data to a table format in PostgreSQL

April 28, 2023

I am trying to convert JSON data to table format in PostgreSQL based on the given JSON. What I have done is below. Based on the given JSON data, I create the following table,

CREATE TABLE example_data (
    service_status TEXT,
    data_status TEXT,
    data_message TEXT,
    id INT,
    name TEXT,
    age INT
);

Use the json_populate_recordset function to insert the JSON data into the table.

INSERT INTO example_data (service_status, data_status, data_message, id, name, age)
SELECT 
    json_data->>'serviceStatus' AS service_status, 
    json_data->>'data.status' AS data_status, 
    json_data->>'data.message' AS data_message, 
    item->>'id' AS id, 
    item->>'name' AS name, 
    item->>'age' AS age
FROM (
    SELECT '{"serviceStatus": "OK", "data": {"status": "OK", "message": "", "item": [{"id": 1, "name": "John", "age": 30},{"id": 2, "name": "Smith", "age": 40}]}}'::json AS json_data
) AS data
CROSS JOIN LATERAL json_populate_recordset(NULL::example_data, json_data->'data'->'item') AS item;

Above query only returning the data in serviceStatus and all other fields are returning null. Can you help me where I am doing wrong. Thanks

>Solution :

This one works, it’s using the correct json paths and using the table layout that you defined in example_data:

SELECT 
    json_data->>'serviceStatus' AS service_status, 
    json_data->'data'->>'status' AS data_status, 
    json_data->'data'->>'message' AS data_message, 
    item.id, -- this not json anymore
    item.name,
    item.age
FROM (
    SELECT '{"serviceStatus": "OK", "data": {"status": "OK", "message": "", "item": [{"id": 1, "name": "John", "age": 30},{"id": 2, "name": "Smith", "age": 40}]}}'::json AS json_data
) AS data
CROSS JOIN LATERAL json_populate_recordset(NULL::example_data, json_data->'data'->'item') AS item;