Efficiently parsing JSON answer into an SQLite table...

[jonsafi]

Hello Everyone,

Am enjoying the benefits of this very nice tool. Lately have been struggling with how to easily parse and add JSON rows into an SQLite table.

The JSON data returned is as follows:

{"totalResults":52372,"financials":
[{"businessId":"0100123-2","financialDate":"2024-05-31","registrationDate":"2024-08-17"},
{"businessId":"0100123-2","financialDate":"2025-05-31","registrationDate":"2025-10-11"},
{"businessId":"0100379-9","financialDate":"2020-12-31","registrationDate":"2021-05-01"},
{"businessId":"0100379-9","financialDate":"2021-12-31","registrationDate":"2022-04-30"},
{"businessId":"0100379-9","financialDate":"2022-12-31","registrationDate":"2023-04-29"},
{"businessId":"0100379-9","financialDate":"2023-12-31","registrationDate":"2024-05-01"},
{"businessId":"0100379-9","financialDate":"2024-12-31","registrationDate":"2025-04-26"},
{"businessId":"0100582-3","financialDate":"2023-03-31","registrationDate":"2023-08-12"},
{"businessId":"0100582-3","financialDate":"2024-03-31","registrationDate":"2024-07-24"},

and am trying to parse it as follows:

WITH raw_dat AS
(SELECT sqlpage.fetch($url) AS json)  
, parsed AS (SELECT 
json_extract(value,'$."financials') AS Business_id
FROM raw_dat, json_each(raw_dat.json)
)

When I check the contents of parsed using:

SELECT count(*) FROM parsed
I get two rows, but running
SELECT * FROM parsed
yields no data.

Any hints or helps would be much appreciated, regards, Sami

[lovasoa]

Hi ! I wrote a guide about json in sql: https://sql-page.com/blog.sql?post=JSON%20in%20SQL%3A%20A%20Comprehensive%20Guide

In your case, I think you want something like:

insert into records(business_id, financial_date, registration_date)
select 
    value->>'businessId',
    value->>'financialDate',
    value->>'registrationDate'
from
    json_each(
        sqlpage.fetch('https://gist.githubusercontent.com/lovasoa/0002076122d14bbf67505dec21426480/raw/9069234052066747d7803e6c636f6b1c81b0cde4/financials.json'),
        '$.financials'
    );

(from https://editor.datapage.app/)

[jonsafi]

Thank you so much for your kind help. Yes, had seen the very comprehensive help, but could not figure out why this code failed. Will try your suggestion and see what happens, very much appreciated :-).