1

I have created a json variable VDN which is like so

{"80001": ["6", "9"], "80002": ["2", "6", "10"], "80004": ["4", "10", "8"]}

I have created a table in which the keys and values separate in two different columns as Varchar.

drop function if exists aa_Dev.VDN();
CREATE OR REPLACE FUNCTION aa_dev.VDN()
  RETURNS VOID AS
$$

begin

    DROP TABLE IF EXISTS aa_dev.sdg_vdn;

    CREATE TABLE IF NOT EXISTS aa_dev.sdg_vdn (
        VDN VARCHAR,
        skills VARCHAR
    );

    INSERT INTO aa_dev.sdg_vdn(VDN, skills)
    select t.*
    from aa_dev.sdg_metadata m
    cross join json_each_text(m.VDN) as t(VDN, "values");
      

   
end

$$
LANGUAGE plpgsql;

So instead of varchar the data types I want should be now

CREATE TABLE IF NOT EXISTS aa_dev.sdg_vdn (
    VDN INT,
    skills INT[]
);

How can I do this in PostgreSQL? I have tried using json_populate_record instead of json_each_text as it can return any data type but that did not work either.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Chloe
  • 127
  • 1
  • 12
  • 1
    Unrelated, but: creating a new table each time a function is invoked is not such a good idea. –  Feb 10 '21 at 10:54

1 Answers1

0

You need a multi step approach: first extract all key/value pairs from the JSON and then convert that back into a JSONB value which in turn can be converted to a record type:

insert into sdg_vdn (vdn, skills)
select (jsonb_populate_record(null::sdg_vdn, to_jsonb(t))).*
from jsonb_each('{"80001": ["6", "9"], "80002": ["2", "6", "10"], "80004": ["4", "10", "8"]}'::jsonb) as t(vdn, skills)

The intermediate step using to_jsonb(t) is necessary to convert the key/value pair that is returned from jsonb_each() back into a single JSON value - which then can be converted to the record type of the target table. The (...).* is necessary to extract the columns of that record.

Online example

  • Why are you converting json to jsonb? – Chloe Feb 10 '21 at 11:07
  • @Chloe: I am not. I am converting the _row_ (with two columns) back into a single json value. (I explained that: "*the intermediate step using to_jsonb() ...*" –  Feb 10 '21 at 11:08
  • I also want to reference the json variable directly from another table, not hard code it as a string. from jsonb_each(aa_dev.sdg_metadata.vdns::jsonb) as t(vdn, skills); but it gives error ERROR: missing FROM-clause entry for table "sdg_metadata" – Chloe Feb 10 '21 at 11:20