Question 114040 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL Function to turn JSON keys into Attribute columns

I have a JSON_TEXT column in my PostgreSQL DB such as this {‘a’:’one’, ‘b’:’two’, ‘c’:’three’}
I would like to create a function that would loop through all of the DISTINCT JSON_object_keys and create a column for each of the keys, and populate all of the values into their new columns. psuedo code example:

create or replace function myFunction (input json_text)
returns //not sure as $$//
BEGIN

    // foreach(key in input)
    //       make and return a column populated with its values somehow idk

END; $$

I understand you can hard code the names of each key and create attributes for them but I have hundreds of keys so this wont be feasible for me.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Found the best answer to this was the following. this is somewhat similar to Eduard's answer just a bit of a different approach.

    DO
$$
DECLARE
    l_keys text;
BEGIN
    DROP TABLE IF EXISTS new_table_name CASCADE;
    SELECT string_agg(DISTINCT format('column_name::json ->> %L as %I',jkey, jkey), ', ')
        INTO l_keys
    FROM old_table_namejson_object_keys(deviceinformation::json) AS t(jkey);
    EXECUTE 'CREATE TABLE new_table_name AS SELECT '||l_keys||' FROM new_table_name';
END;
$$;

    this took a JSON text column and put every one of its keys and their associated values into their own columns in a new table, ta-da.


  2. 0

    Your request looks like a pivot table with a list of columns not defined at the run time. You can get your result by creating a composite type dynamically corresponding to the list of json keys and then by using the standard json function json_to_record :

    CREATE OR REPLACE PROCEDURE create_composite_type(input json) LANGUAGE plpgsql AS $$
DECLARE
  column_list text ;
BEGIN
  SELECT string_agg(DISTINCT quote_ident(i) || ' text', ',')
    INTO column_list
    FROM json_object_keys(input) AS i ;
  
  DROP TYPE IF EXISTS composite_type ;
  EXECUTE 'CREATE TYPE composite_type AS (' || column_list || ')' ;
END ;
$$ ;

CALL create_composite_type(input) ;
SELECT * FROM json_populate_record( null :: composite_type, input) ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search