let’s say I have a self-declared composite type ‘address’ and I get f.e. a variable ‘customers’, where an array of addresses is just stored as plain object. How would I insert this into a table with a column of type ‘address[]’ into a single row?
(I tried it with json_populate_recordset(null::address, $1) and JSON.stringify(customers), but that didn’t work.)
You’re on the right track with the json_populate_recordset()
idea. You can use it to unpack the json
array as a plain set of address
, then pack it back up into an address[]
array using array_agg()
:
select array_agg(an_address) as array_of_addresses
from json_populate_recordset(null::address,$3) as setofaddresses(an_address);
Note that if you pass an empty array as $3 to the query above, it’ll have a null
as the array_of_addresses
. ARRAY()
constructor syntax lets you plug a subselect into it, shortening that to:
select array(select json_populate_recordset(null::address,'[]') );
Which gets you an empty array in cases where $3 is an empty array, instead of the previous null
.
Here’s an example:
create type address as (
street_name text
, street_no int
, zip_code text
, city text
, state text);
create table subscribers (
id bigint generated by default as identity primary key
, first_name text
, last_name text
, addresses address[]);
prepare insert_into_subscribers(text, text, json) as
insert into subscribers(first_name, last_name, addresses)
values($1, $2, array(select json_populate_recordset(null::address,$3)) );
execute insert_into_subscribers( 'John'
,'Doe'
,'[ { "street_name": "Sesame Street"
,"street_no":3
,"zip_code":"432ABC234"
,"city":"New York"
,"state":"NY"}
,{ "street_name": "Lincoln Boulevard"
,"street_no":4
,"zip_code":"432ABC235"
,"city":"Albuquerque"
,"state":"NM"}]');
select*from subscribers;
id | first_name | last_name | addresses |
---|---|---|---|
1 | John | Doe | {“(“Sesame Street”,3,432ABC234,”New York”,NY)”,”(“Lincoln Boulevard”,4,432ABC235,Albuquerque,NM)”} |
select id, first_name, last_name, addr.*
from subscribers
cross join lateral unnest(addresses)with ordinality as addr;
id | first_name | last_name | street_name | street_no | zip_code | city | state | ordinality |
---|---|---|---|---|---|---|---|---|
1 | John | Doe | Sesame Street | 3 | 432ABC234 | New York | NY | 1 |
1 | John | Doe | Lincoln Boulevard | 4 | 432ABC235 | Albuquerque | NM | 2 |
6