process nested json

[setop]

Hi all,

I have an API that produce nested date : people having addresses.

{
  "people": [
    ...
    {
      "id": 23469,
      "name": "Charlie Brown",
      "addresses": [
        {
          "street": "321 Birch St",
          "city": "Madison",
          "state": "WI",
          "zip": "53703"
        },
        {
          "street": "654 Cedar St",
          "city": "Madison",
          "state": "WI",
          "zip": "53704"
        }
      ]
    },
    ...
  ]
}

I load it with sqlpage.fetch.

set res = sqlpage.fetch('https://api.example.com/v1/get_people');

I'd like to insert all data in people and address tables respectively.

I'm not sure how to process.

[heejit]

if your using postgresql you can use following function which convert json to a table
jsonb_to_recordset

[lovasoa]

Hi @setop ! Which database are you using? different databases have different json functions.

You may be interested in https://sql-page.com/blog.sql?post=JSON%20in%20SQL%3A%20A%20Comprehensive%20Guide

[lovasoa]

Here is an example of how to do it in SQLite

-- Create the People table
CREATE TABLE IF NOT EXISTS People (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- Create the Addresses table
CREATE TABLE IF NOT EXISTS Addresses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    person_id INTEGER,
    street TEXT,
    city TEXT,
    state TEXT,
    zip TEXT,
    FOREIGN KEY (person_id) REFERENCES People(id)
);

SET people = json('{"people": [
   {"id": 23469, "name": "Charlie Brown", "addresses": [
        {"street": "321 Birch St", "city": "Madison", "state": "WI", "zip": "53703"},
        {"street": "654 Cedar St", "city": "Madison", "state": "WI", "zip": "53704"}
  ]}
]}');

-- Insert data from JSON into People table
INSERT INTO People (id, name)
SELECT 
    json_extract(value, '$.id'),
    json_extract(value, '$.name')
FROM json_data, json_each(json_extract($people, '$.people'));

-- Insert data from JSON into Addresses table
WITH flattened_addresses AS (
    SELECT 
        json_extract(p.value, '$.id') AS person_id,
        a.value AS address
    FROM 
        json_each(json_extract($people, '$.people')) AS p,
        json_each(json_extract(p.value, '$.addresses')) AS a
)
INSERT INTO Addresses (person_id, street, city, state, zip)
SELECT 
    person_id,
    json_extract(address, '$.street'),
    json_extract(address, '$.city'),
    json_extract(address, '$.state'),
    json_extract(address, '$.zip')
FROM flattened_addresses;