WITH RECURSIVE extract_all AS (

	SELECT
		id,
		jsonb_typeof(value) AS typ,
		key,
		key AS path,
		value
	FROM
		table_with_json_col,
		jsonb_each(json_col)
		
		
	UNION ALL
	
	
	SELECT
		id,
		jsonb_typeof(coalesce(arr_value, obj_value)) as typ,
		key || CASE
			WHEN obj_key IS NOT NULL THEN ('.' || obj_key)
			ELSE ''
		END as key,
		path || '.' || coalesce(obj_key, (arr_key - 1)::text) as path,
		coalesce(arr_value, obj_value) as value
		
	FROM extract_all
	
	LEFT JOIN LATERAL jsonb_array_elements(
		CASE 
			WHEN typ = 'array' THEN value
			ELSE NULL
		END
	)
	WITH ORDINALITY AS a (arr_value, arr_key)
	ON typ = 'array'
	
	
	LEFT JOIN LATERAL jsonb_each(
		CASE 
			WHEN typ = 'object' THEN value
			ELSE NULL
		END
	) AS o (obj_key, obj_value)
	ON typ = 'object'
	
	WHERE typ IN ('array', 'object'))
SELECT
	id,
	table_name,
	typ,
	key,
	path,
	CASE
		WHEN typ = 'string'
		THEN JSON_VALUE (value, '$')::text
		
		ELSE NULL::text
	END AS value_text,
	
	CASE
		WHEN typ = 'number'
		THEN JSON_VALUE (value, '$')::numeric
		
		ELSE NULL::numeric
	END AS value_number,
	
	CASE 
		WHEN typ = 'boolean'
		THEN JSON_VALUE (value, '$')::bool
		
		ELSE NULL::bool
	END AS value_boolean
FROM
	extract_all
WHERE
	typ IN ('string', 'null', 'number', 'boolean');