Postgresql json column to view

Question

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

Answer 1

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.

Answer 2

@a_horse_with_no_name, thanks a lot!

I want to add some details, that I encountered trying to create view of array of jsonb objects:

1. If your data type is jsonb than you need to use same functions like above, but instead json use jsonb. So json_object_keys turn into jsonb_object_keys.
2. If you have not just json/jsonb object, but array of json/jsonb objects, you can use this script:

DO $$
DECLARE l_keys text;
BEGIN
  drop view if exists YOUR_VIEW_NAME cascade;

  select string_agg(distinct format('jerrayel ->> %L as %I',jkey, jkey), ', ')
     into l_keys
  from YOUR_TABLE_NAME, jsonb_array_elements(YOUR_JSONB_COLUMN_NAME) as t(jerrayel), jsonb_object_keys(t.jerrayel) as a(jkey);

  execute 'create view YOUR_VIEW_NAME as select '||l_keys||' from YOUR_TABLE_NAME, jsonb_array_elements(YOUR_JSONB_COLUMN_NAME) as t(jerrayel)';
END$$;