Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(
   id   int NOT NULL PRIMARY KEY
  ,data jsonb
);
INSERT INTO mytable(id,data) VALUES
 (1,'{"hi": "mom", "age": 43, "bar": true, "nested1": {"foo": 10.5, "nested2": {"zoo":"deep"}}}')
,(2,'{"bar": false, "nested1": {"foo": -0.5, "nested2": {"zoo":"none"}}}')
,(3,'{"bye": "dad", "age": 41}');

CREATE TABLE



INSERT 0 3


select * from mytable


id
data


1
{"hi": "mom", "age": 43, "bar": true, "nested1": {"foo": 10.5, "nested2": {"zoo": "deep"}}}

2
{"bar": false, "nested1": {"foo": -0.5, "nested2": {"zoo": "none"}}}

3
{"age": 41, "bye": "dad"}



SELECT 3


-- One level example

  select
    jsonb_object_keys(data) as col_name,
    jsonb_typeof(data->jsonb_object_keys(data)) as col_type
  from mytable


col_name
col_type


hi
string

age
number

bar
boolean

nested1
object

bar
boolean

nested1
object

age
number

bye
string



SELECT 8


-- Nested JSON

WITH RECURSIVE cte AS (
  select
    jsonb_object_keys(data) as col_name,
    jsonb_object_keys(data) as col_path,
    jsonb_typeof(data->jsonb_object_keys(data)) as col_type,
    data
  from mytable
  union all
  select
    jsonb_object_keys(data->col_name) as col_name,
    col_path || '_' || jsonb_object_keys(data->col_name) as col_path, 
    jsonb_typeof(data->col_name->jsonb_object_keys(data->col_name)) as col_type,
    data->cte.col_name AS data
  from cte
  where col_type = 'object'
) 
SELECT distinct col_path AS col_name, col_type 
FROM cte
ORDER BY 1 


col_name
col_type


age
number

bar
boolean

bye
string

hi
string

nested1
object

nested1_foo
number

nested1_nested2
object

nested1_nested2_zoo
string



SELECT 8


-- SQL helper

WITH RECURSIVE cte AS (
  select
    jsonb_object_keys(data) as col_name,
    'data->' as col_path,
    jsonb_typeof(data->jsonb_object_keys(data)) as col_type,
    data
  from mytable
  union all
  select
    jsonb_object_keys(data->cte.col_name) as col_name,
    col_path || '''' || col_name || '''->' as col_path,
    jsonb_typeof(data->cte.col_name->jsonb_object_keys(data->cte.col_name)) as col_type,
    data->cte.col_name AS data
  from cte
  where cte.col_type = 'object'
) 
SELECT 
  'CREATE TABLE newtable AS SELECT id, ' || 
   STRING_AGG(distinct 
     E'\n  (' || col_path || '>''' || col_name || ''')::' || 
     CASE col_type
        WHEN 'number' THEN 'int'
        WHEN 'string' THEN 'text'
        ELSE col_type
      END ||' AS ' || REPLACE(REPLACE(col_path, '->', '_'), '''', '') || col_name,
      ', '
   ) || E'\nFROM mytable' as sql
FROM cte
WHERE col_type <> 'object';


sql


CREATE TABLE newtable AS SELECT id, 
  (data->>'age')::int AS data_age, 
  (data->>'bar')::boolean AS data_bar, 
  (data->>'bye')::text AS data_bye, 
  (data->>'hi')::text AS data_hi, 
  (data->'nested1'->>'foo')::int AS data_nested1_foo, 
  (data->'nested1'->'nested2'->>'zoo')::text AS data_nested1_nested2_zoo
FROM mytable



SELECT 1


CREATE TABLE newtable AS SELECT id, 
  (data->>'age')::int AS data_age, 
  (data->>'bar')::boolean AS data_bar, 
  (data->>'bye')::text AS data_bye, 
  (data->>'hi')::text AS data_hi, 
  (data->'nested1'->>'foo')::float AS data_nested1_foo,                          -- changed
  (data->'nested1'->'nested2'->>'zoo')::text AS data_nested1_nested2_zoo
FROM mytable

SELECT 3


select * from newtable


id
data_age
data_bar
data_bye
data_hi
data_nested1_foo
data_nested1_nested2_zoo


1
43
t
null
mom
10.5
deep

2
null
f
null
null
-0.5
none

3
41
null
dad
null
null
null



SELECT 3

id	data
1	{"hi": "mom", "age": 43, "bar": true, "nested1": {"foo": 10.5, "nested2": {"zoo": "deep"}}}
2	{"bar": false, "nested1": {"foo": -0.5, "nested2": {"zoo": "none"}}}
3	{"age": 41, "bye": "dad"}

col_name	col_type
hi	string
age	number
bar	boolean
nested1	object
bar	boolean
nested1	object
age	number
bye	string

id	data_age	data_bar	data_bye	data_hi	data_nested1_foo	data_nested1_nested2_zoo
1	43	t	null	mom	10.5	deep
2	null	f	null	null	-0.5	none
3	41	null	dad	null	null	null