Como você expande uma tabela em que uma coluna é uma chave e a outra é uma matriz json de valores em um conjunto de registros de colunas de chave/valor?

Link do violino: https://dbfiddle.uk/HlySSJ58

Tabelas e tipos

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

Dados de entrada

[
  {
    "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"
      }
    ]
  }
]

Consulta

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

O problema

No profile_initsCTE eu preciso transformar esta tabela:

nesta:

Assim, pode ser selecionado para a consulta de inserção emnew_profiles CTE e posteriormente fornecer um mapeamento entre inicializadores e IDs de entidade criados para inserir relações.