9

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

0

2 Answers 2

8

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.

1
  • Generating a view is a nice idea but my keys are too variable.. Thanks anyway, I will just save my data in many columns and not one json column to avoid this issue. This might be more complicated while importing the data but in the end the whole thing should work as a stand alone solution, so creating those types by hand is no option Oct 10, 2016 at 11:27
1

@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$$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.