Convert json to nested composite postgres type

Question

I have the following nested types defined in postgres:

CREATE TYPE address AS ( name text, street text, zip text, city text, country text ); CREATE TYPE customer AS ( customer_number text, created timestamp WITH TIME ZONE, default_billing_address address, default_shipping_address address ); 

And now I would like to populate these types in a stored procedure that receives json as an input parameter. This works for fields at the top level, the output displays the internal format of the composite postgres type:

 # select json_populate_record(null::customer, '{"customer_number":"12345678"}'::json)::customer; json_populate_record ---------------------- (12345678,,,) (1 row) 

However, postgres does not handle the json nested structure:

 # select json_populate_record(null::customer, '{"customer_number":"12345678","default_shipping_address":{"name":"","street":"","zip":"12345","city":"Berlin","country":"DE"}}'::json)::customer; ERROR: malformed record literal: "{"name":"","street":"","zip":"12345","city":"Berlin","country":"DE"}" DETAIL: Missing left parenthesis. 

Which works again if the attached property is in the internal postgres format, for example here:

 # select json_populate_record(null::customer, '{"customer_number":"12345678","default_shipping_address":"(\"\",\"\",12345,Berlin,DE)"}'::json)::customer; json_populate_record -------------------------------------------- (12345678,,,"("""","""",12345,Berlin,DE)") (1 row) 

Is there a way to get postgres to convert from a nested json structure to the corresponding composite type?

Answer 1

plpython to the rescue:

 create function to_customer (object json) returns customer AS $$ import json return json.loads(object) $$ language plpythonu; 

Example:

 select to_customer('{ "customer_number":"12345678", "default_shipping_address": { "name":"", "street":"", "zip":"12345", "city":"Berlin", "country":"DE" }, "default_billing_address":null, "created": null }'::json); to_customer -------------------------------------------- (12345678,,,"("""","""",12345,Berlin,DE)") (1 row) 

Warning: postgresql when creating a return object from python requires all null values ​​to be in the form None (i.e. it is not allowed to skip null values ​​as it is not), so we must specify all null values ​​in the incoming JSON. For example, it is not allowed:

 select to_customer('{ "customer_number":"12345678", "default_shipping_address": { "name":"", "street":"", "zip":"12345", "city":"Berlin", "country":"DE" } }'::json); ERROR: key "created" not found in mapping HINT: To return null in a column, add the value None to the mapping with the key named after the column. CONTEXT: while creating return value PL/Python function "to_customer" 

Answer 2

Use json_populate_record() only for nested objects:

 with a_table(jdata) as ( values ('{ "customer_number":"12345678", "default_shipping_address":{ "name":"", "street":"", "zip":"12345", "city":"Berlin", "country":"DE" } }'::json) ) select ( jdata->>'customer_number', jdata->>'created', json_populate_record(null::address, jdata->'default_billing_address'), json_populate_record(null::address, jdata->'default_shipping_address') )::customer from a_table; row -------------------------------------------- (12345678,,,"("""","""",12345,Berlin,DE)") (1 row) 

---

Nested composite types are not intended for Postgres (and any RDBMS). They are too complicated and troublesome. In database logic, nested structures should be supported as linked tables, for example.

 create table addresses ( address_id serial primary key, name text, street text, zip text, city text, country text ); create table customers ( customer_id serial primary key, -- not necessary `serial` may be `integer` or `bigint` customer_number text, -- maybe redundant created timestamp with time zone, default_billing_address int references adresses(address_id), default_shipping_address int references adresses(address_id) ); 

It is sometimes wise to have a nested structure in the table, but it seems more convenient and natural to use jsonb or hstore in these cases, for example:

 create table customers ( customer_id serial primary key, customer_number text, created timestamp with time zone, default_billing_address jsonb, default_shipping_address jsonb ); 

Answer 3

This is similar to Postgres 10 solution. Searching for release notes for json_populate_record shows the following change:

Make json_populate_record () and related functions process JSON arrays and objects recursively (Nikita Glukhov)

With this change, the fields of the array type in the target SQL type are correctly converted from JSON arrays, and the fields of the composite type are correctly converted from JSON objects. Previously, such cases failed because the text representation of the JSON value would be passed to array_in () or record_in (), and its syntax would not match the expectations of those input functions.