Fetching recordset from array

Question

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.

Answer 1

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