Converting json to nested postgres composite 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 would now like to populate this types in a stored procedure, which gets json as an input parameter. This works for fields on the top-level, the output shows me the internal format of a postgres composite type:

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

However, postgres does not handle a nested json 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.

What works again is, if the nested property is in postgres' internal format like 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 any way to get postgres to convert from a nested json structure to a corresponding composite type?

Answer 1

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 what Postgres (and any RDBMS) was designed for. They are too complicated and troublesome. In the database logic nested structures should be maintained as related tables, e.g.

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)
);

Sometimes it is reasonable to have nested structure in a table but it seems more convenient and natural to use jsonb or hstore in these cases, e.g.:

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