如何将一列是键，另一列是 json 值数组的表扩展为键/值列的记录集？

小提琴链接：https ://dbfiddle.uk/HlySSJ58

表格和类型

CREATE TYPE profile_init AS (
  name text,
  description text
);

CREATE TYPE account_init AS (
  login text,
  password text,
  email text,
  -- an array of `profile_init`s
  profile_inits json
);

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  init_index bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  login text NOT NULL,
  password text NOT NULL,
  email text
);

CREATE TABLE profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  init_index bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  name text,
  description text
);

CREATE TABLE account_profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  account_id bigint NOT NULL REFERENCES accounts,
  profile_id bigint NOT NULL REFERENCES profiles
);

输入数据

[
  {
    "login": "3ED4ECBBC9",
    "password": "E67EDDB6033D02140BB4",
    "email": "a@b",
    "profile_inits": null
  },
  {
    "login": "C86D7E2CF0",
    "password": "75404617C000A0EB070C",
    "profile_inits": [
      {
        "name":"C1B549E",
        "description":"1313CB6F876EA62837A15C20D78A8FA3FC926008FA289AE722"
      }
    ]
  },
  {
    "login": "C51D77BF87",
    "password": "605509993A05EE393081",
    "email": null,
    "profile_inits": [
      {},
      { "name": "2E35813" },
      { "description": "AF2A372263233827253DE19DB8E12798EEF59C311CFE9CEFAB" },
      { "name": "EE899CD", "description": null },
      { "name": null, "description": "CB4CEE63293E16988E58805FED223943E7CAFDF6F417393B30" }
    ]
  },
  {
    "login": "4229D47E2C",
    "password": "CE4E14ED6AD77CBC71B5",
    "email": "b@c",
    "profile_inits": [
      {
        "name": "956F079",
        "description": "BC1E803500773076940C0052D289AAB9952DD47D4954447C8E"
      },
      {
        "name": "99B327B",
        "description": "C4C9702836B1E05CC49D4E205CC8292D017FF3C1BE179CA435"
      },
      {
        "name": "D8EF1A8",
        "description": "554A01F7DBA0C889AF014CBF7EA938DED791FB3A3A50C932E5"
      },
      {
        "name": "91151DB",
        "description": "A86CA083BD509F23FD515C045C8BE32D4B57E3A3940FB8BFD4"
      },
      {
        "name": "31EC363",
        "description": "7008C341EDDBB93B3B1D5904E5EF1FCAE01EB25AC2A5E51761"
      },
      {
        "name": "E7E11D7",
        "description": "0C313B46ADD0E946D24854EA5651379C9D4D56656BFBC6312F"
      },
      {
        "name": "32F1C7C",
        "description": "5B08641B0A3F7359C929E14EEE58502DACA40CF830FF923A7B"
      },
      {
        "name": "23C85ED",
        "description": "7CB34E28022DD84E96D9825CD5E0CB0774D548F56762CF2A6C"
      },
      {
        "name": "1800D37",
        "description": "589850742C3A3A1FC2E9130494069847CCB426636B7F7440F4"
      }
    ]
  }
]

询问

-- transform top level json array into a set of records
WITH account_inits AS (
  SELECT
    row_number () OVER () as account_init_index,
    login,
    password,
    email,
    profile_inits
  FROM
    json_to_recordset(
      $json$
      ...
      $json$
    ) AS input_init(
      login text,
      password text,
      email text,
      profile_inits json
    )
),
-- transform nested profile inits into profile inits
profile_inits AS (
  SELECT
    -- need a reference to original account init
    -- for later joins
    account_init_index,
    row_number () OVER () as profile_init_index,
    name,
    description
    -- this json array has to be expanded
    -- and joined on `account_init_index`
    -- on itself I presume
    profile_inits
  FROM
    account_inits
),
-- create new accounts
new_accounts AS (
  INSERT INTO accounts
    (
      init_index,
      login,
      password,
      email
    )
  SELECT
    account_init_index AS init_index,
    login,
    password,
    email
  FROM
    account_inits
  RETURNING
    *
),
-- create new profiles
new_profiles AS (
  INSERT INTO profiles
    (
      init_index,
      name,
      description
    )
  SELECT
    profile_init_index AS init_index,
    name,
    description
  FROM
    profile_inits
  RETURNING
    *
),
-- create new profile account relations
new_account_profiles AS (
  -- join new accounts and their inits
  WITH input_accounts AS (
    SELECT
      account_inits.account_init_index,
      new_accounts.id AS account_id
    FROM
      account_inits
      INNER JOIN
      new_accounts
      ON
        account_inits.account_init_index = new_accounts.init_index
  ),
  -- join new profiles and their inits
  input_profiles AS  (
    SELECT
      profile_inits.account_init_index,
      profile_inits.profile_init_index,
      new_profiles.id AS profile_id
    FROM
      profile_inits
      INNER JOIN
      new_profiles
      ON
        profile_inits.profile_init_index = new_profiles.init_index

  ),
  -- join inputs
  account_profile_pairs AS (
    SELECT
      input_accounts.account_id,
      input_profiles.profile_id
    FROM
      input_accounts
      INNER JOIN
      input_profiles
      ON
        input_accounts.account_init_index = input_profiles.account_init_index
  )
  INSERT INTO account_profiles
    (
      account_id,
      profile_id
    )
  SELECT
    account_id,
    profile_id
  FROM
    account_profile_pairs
  RETURNING
    *
)
SELECT
  (
    SELECT
      count(*)
    FROM
      new_accounts
  ) AS new_accounts_count,
  (
    SELECT
      count(*)
    FROM
      new_profiles
  ) AS new_profiles_count,
  (
    SELECT
      count(*)
    FROM
      new_account_profiles
  ) AS new_account_profiles_count
;

问题

在profile_initsCTE，我需要转换此表：

进入这个：

因此可以选择它用于new_profilesCTE 中的插入查询，然后在初始化器和创建的实体 ID 之间提供映射以插入 realtions。