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?

Rhettrhetta

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 building returned object from python requires to have all null values present as None (ie. it's not allowed to skip null values as not present), thus we have to specify all null values in incoming json. For example, 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"

Unreasonable

While I probably don't want to rely on plpython for this problem, this answer shows that there is a generic solution and that postgres would have all the type information needed to implement it. – Dithionite

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

Emogeneemollient

This would work, but for bigger or more deeply nested objects I'd like to avoid repeating all columns and their order in the composite type. – Dithionite

I'm affraid you have no choice. – Emogeneemollient

To give some more context on the use of nested types, those would be the return values of a stored procedure or query, the underlying tables would be nicely normalized. Zalando is doing something similar on the client side in their java-sproc-wrapper (github.com/zalando-incubator/java-sproc-wrapper/#type-mapping), but this first has to extract all the type information from the database. By mapping the types to and from json, the implementation could be much simpler. – Dithionite

That's misunderstanding. If the server has to response with structured data it should send simple json which can be easily interpreted in the client side without any additional tools. Still I do not see any reason to use nested composite types. You have no means to map them into related tables on the server side because this is completely useless. Well, I can imagine the use of nested composite type for a common used type of objects but this needs creating special tools to support (compose, decompose, assign, modify, compare) such data. – Emogeneemollient

Answer 3

This seems to be solved in Postgres 10. Searching the 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, array-type fields in the destination SQL type are properly converted from JSON arrays, and composite-type fields are properly converted from JSON objects. Previously, such cases would fail because the text representation of the JSON value would be fed to array_in() or record_in(), and its syntax would not match what those input functions expect.

Rhettrhetta