I'm having a column in postgresql (Table-Information) containing json format data inside an array. The column also contains NULL data Like:
Location
NULL
[{"city":"Mumbai","state":"Maharashtra","country":"India"}]
[{"city":"Pune","state":"Maharashtra","country":"India"}]
NULL
[{"city":"Bhopal","state":"Madhya Pradesh","country":"India"}]
I want to convert it into a table with keys as column name and values as rows.
Output
city state country
Mumbai Maharashtra India
Pune Maharashtra India
Bhopal Madhya Pradesh India
How to get the desired output
I used unnest but it's not working, while have to hard code data for json_to_recordset.
unnest
applies to sql arrays. For json arrays you have to use json processing functions
If all your json records have the same set of keys, then you can use json_populate_record
after having created dynamically the right composite type :
CREATE OR REPLACE PROCEDURE create_composite_type() LANGUAGE plpgsql AS $$
DECLARE
column_list text ;
BEGIN
SELECT string_agg(DISTINCT quote_ident(key) || ' text', ',')
INTO column_list
FROM mytable
CROSS JOIN LATERAL json_object_keys(location->0) AS key
LIMIT 1 ;
DROP TYPE IF EXISTS composite_type ;
EXECUTE 'CREATE TYPE composite_type AS (' || column_list || ')' ;
END ;
$$ ;
CALL create_composite_type() ;
SELECT (json_populate_record( null :: composite_type, location->0)).*
FROM mytable;
Result
city | country | state |
---|---|---|
Mumbai | India | Maharashtra |
Pune | India | Maharashtra |
Bhopal | India | Madhya Pradesh |
see dbfiddle