Блог

У меня есть следующие вложенные типы, определенные в 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
);
  

И теперь хотелось бы заполнить эти типы в хранимой процедуре, которая получает json в качестве входного параметра. Это работает для полей верхнего уровня, на выходе отображается внутренний формат составного типа postgres:

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

Однако postgres не обрабатывает вложенную структуру json:

 # 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.
  

Что снова работает, так это то, что вложенное свойство находится во внутреннем формате postgres, как здесь:

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

Есть ли какой-либо способ заставить postgres преобразовать вложенную структуру json в соответствующий составной тип?

Использовать json_populate_record() только для вложенных объектов:

 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) 
  

Вложенные составные типы — это не то, для чего был разработан Postgres (и любая другая СУБД). Они слишком сложны и хлопотны.
В логике базы данных вложенные структуры должны поддерживаться в виде связанных таблиц, например

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

Иногда разумно иметь вложенную структуру в таблице, но это кажется более удобным и естественным для использования jsonb или hstore в этих случаях, например:

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

plpython на помощь:

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

Пример:

 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)
  

Предупреждение: postgresql при создании возвращаемого объекта из python требует, чтобы все null значения присутствовали как None (т. е.. недопустимо пропускать значения null как отсутствующие), поэтому мы должны указать все значения null во входящем json. Например, недопустимо:

 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"
  

Похоже, это решается в Postgres 10. Поиск в примечаниях к выпуску json_populate_record показывает следующее изменение:

Заставить json_populate_record() и связанные функции рекурсивно обрабатывать массивы и объекты JSON (Никита Глухов)

С этим изменением поля типа массива в целевом типе SQL правильно преобразуются из массивов JSON, а поля составного типа правильно преобразуются из объектов JSON. Ранее такие случаи завершались неудачей, потому что текстовое представление значения JSON передавалось в array_in() или record_in(), и его синтаксис не соответствовал ожидаемому этими функциями ввода.