PostgreSQL – Convert JSON Column to View

jsonpostgresqlpostgresql-9.5

I have a table that looks like this

id |   json_column
---+------------
 1 | {"text1":"a", "text2":"b", "text3":"c", ....}
 2 | {"text1":"b", "text2":"c", ....}

and I want it to be like this

text1 | text2 | text3 | ....
a     | b     | c     | ....
b     | c     | d     | ....

one variant I found to get this result is using json_populate_record() and creating a new type x as (text1 text, text2 text, text3 text,….) so i can execute

select (json_populate_record(null::x, json_column)).* from table

Is there another way to do this without creating a type like above? All new columns can have the type text, so is there a shortcut maybe?

My problem is that I have 50 keys in one json field in the form of aaaa_bbbb_cccc_d so creating one type for this by hand would take way to much time

I am using 9.5.4

Best Answer

There is no way that saves you from specifying all JSON attributes in the select list, whether implicitly through the "dummy type" or explicitly, e.g. using something like this:

select json_column ->> 'text1' as text1, 
       json_column ->> 'text2' as text2, 
       ... 
from the_table;

What you can do, is to make this simpler by automatically creating a view with all attributes based on the distinct attributes in the JSON documents.

The following code will re-create a view with all distinct keys from the JSON column:

do
$$
declare
  l_keys text;
begin
   drop view if exists v_json_view cascade;

   select string_agg(distinct format('json_column ->> %L as %I',jkey, jkey), ', ')
     into l_keys
   from the_table, json_object_keys(json_column) as t(jkey);

   execute 'create view v_json_view as select '||l_keys||' from the_table';
end;
$$
;

You will need to run the above every time the list of keys in all json documents changes. Theoretically this could be done in a trigger but that's probably not a good idea if you run many updates on that table.

If the total number of JSON keys is somewhat "stable", you could schedule a cron job to re-create that view in regular intervals.

You are also limited by the maximum number of columns in a table or view. If you have more (distinct) keys than approx. 1600 (maybe less) the above will fail.

Related Question