0

Fiddle link: https://dbfiddle.uk/HlySSJ58

Tables and Types

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

Input data

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

Query

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

The problem

At profile_inits CTE I need to transform this table:

account_init_index login password email profile_inits
1 3ED4ECBBC9 E67EDDB6033D02140BB4 a@b null
2 C86D7E2CF0 75404617C000A0EB070C null [{"name":"C1B549E","description":"1313CB6F876EA62837A15C20D78A8FA3FC926008FA289AE722"}]
3 C51D77BF87 605509993A05EE393081 null [{},{ "name": "2E35813" },{ "description": "AF2A372263233827253DE19DB8E12798EEF59C311CFE9CEFAB" },{ "name": "EE899CD", "description": null },{ "name": null, "description":"CB4CEE63293E16988E58805FED223943E7CAFDF6F417393B30" }]
4 4229D47E2C CE4E14ED6AD77CBC71B5 b@c [{"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"}]

into this one:

account_init_index profile_init_index name description
2 1 C1B549E 1313CB6F876EA62837A15C20D78A8FA3FC926008FA289AE722
3 2 null null
3 3 2E35813 null
3 4 null AF2A372263233827253DE19DB8E12798EEF59C311CFE9CEFAB
3 5 EE899CD null
3 6 null CB4CEE63293E16988E58805FED223943E7CAFDF6F417393B30
4 7 956F079 BC1E803500773076940C0052D289AAB9952DD47D4954447C8E
4 8 99B327B C4C9702836B1E05CC49D4E205CC8292D017FF3C1BE179CA435
4 9 D8EF1A8 554A01F7DBA0C889AF014CBF7EA938DED791FB3A3A50C932E5
4 10 91151DB A86CA083BD509F23FD515C045C8BE32D4B57E3A3940FB8BFD4
4 11 31EC363 7008C341EDDBB93B3B1D5904E5EF1FCAE01EB25AC2A5E51761
4 12 E7E11D7 0C313B46ADD0E946D24854EA5651379C9D4D56656BFBC6312F
4 13 32F1C7C 5B08641B0A3F7359C929E14EEE58502DACA40CF830FF923A7B
4 14 23C85ED 7CB34E28022DD84E96D9825CD5E0CB0774D548F56762CF2A6C
4 15 1800D37 589850742C3A3A1FC2E9130494069847CCB426636B7F7440F4

So it could be selected for the insert query in new_profiles CTE and later on provide a mapping between initializers and created entity IDs to insert realtions.

Improve this question
4
  • This is kinda cringe, the markdown tables looked like tables in the preview.
    – Biller Builder
    Oct 25, 2022 at 8:45
  • The table formatting only works if there is an empty line immediately before it
    – a_horse_with_no_name
    Oct 25, 2022 at 8:57
  • Yeah I just found out.
    – Biller Builder
    Oct 25, 2022 at 8:57
  • @a_horse_with_no_name I accidentally found the solution but it only brings more questions in regards to query semantics. Do I have to edit this question or a create a new one while closing the current?
    – Biller Builder
    Oct 26, 2022 at 14:37

1 Answer 1

Reset to default
0

I accidentally stumbled upon the solution using a CROSS JOIN:

profile_inits AS (
  SELECT
    account_inits.account_init_index,
    row_number () OVER () as profile_init_index,
    profile_init.name,
    profile_init.description
  FROM
    account_inits
    CROSS JOIN
    json_to_recordset(
      account_inits.profile_inits
    ) AS profile_init(
      name text,
      description text
    )
),

I was a bit confused why it did exactly what I needed with no additional qualifiers. I assumed it would include account inits without profile inits too. But I guess CROSS JOIN just completely ignores the NULL tables and json_to_recordset() also swallows up NULL inputs. Outside of this particular case it will probably require NULL checks everywhere.

Full fiddle there: https://dbfiddle.uk/FFIaSLoz

Improve this answer
2
  • 1
    There is an implicit LATERAL on your CROSS JOIN as the target is a table-returning function.
    – dwhitemv
    Oct 30, 2022 at 19:17
  • Are there other implicit parts in this SQL statement which need to be explicit in the example?
    – Biller Builder
    Oct 30, 2022 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.