I have another problem I would like assistance with dbfiddle.uk/5besOGoU
I can’t get the tables to work for the Balance_Sheet.
<code>SELECT
j1.*
-- ,j2.*
,j3.*
FROM OPENJSON(@response)
WITH (
request nvarchar(max),
[type] nvarchar(max),
results nvarchar(max) AS JSON
) j1
outer APPLY OPENJSON(j1.results)
WITH (
results_type nvarchar(100) '$.type',
results_id nvarchar(100) '$.id',
segmentation nvarchar(max) '$.tables.segmentation' AS JSON,
financial_statement_restate_json nvarchar(max) '$.tables.financial_statements_restate' AS JSON
) j2
outer APPLY OPENJSON(j2.segmentation)
WITH (
[period_3m_currency_id] nvarchar(max) '$.period_3m.currency_id',
[period_3m_fiscal_year_end] nvarchar(max) '$.period_3m.fiscal_year_end',
[period_3m_period] nvarchar(max) '$.period_3m.period',
[period_3m_report_type] nvarchar(max) '$.period_3m.report_type',
[period_3m_accounts_payable] nvarchar(max) '$.period_3m.accounts_payable',
[period_3m_accounts_receivable] nvarchar(max) '$.period_3m.accounts_receivable'
)
j3
</code>
<code>SELECT
j1.*
-- ,j2.*
,j3.*
FROM OPENJSON(@response)
WITH (
request nvarchar(max),
[type] nvarchar(max),
results nvarchar(max) AS JSON
) j1
outer APPLY OPENJSON(j1.results)
WITH (
results_type nvarchar(100) '$.type',
results_id nvarchar(100) '$.id',
segmentation nvarchar(max) '$.tables.segmentation' AS JSON,
financial_statement_restate_json nvarchar(max) '$.tables.financial_statements_restate' AS JSON
) j2
outer APPLY OPENJSON(j2.segmentation)
WITH (
[period_3m_currency_id] nvarchar(max) '$.period_3m.currency_id',
[period_3m_fiscal_year_end] nvarchar(max) '$.period_3m.fiscal_year_end',
[period_3m_period] nvarchar(max) '$.period_3m.period',
[period_3m_report_type] nvarchar(max) '$.period_3m.report_type',
[period_3m_accounts_payable] nvarchar(max) '$.period_3m.accounts_payable',
[period_3m_accounts_receivable] nvarchar(max) '$.period_3m.accounts_receivable'
)
j3
</code>
SELECT
j1.*
-- ,j2.*
,j3.*
FROM OPENJSON(@response)
WITH (
request nvarchar(max),
[type] nvarchar(max),
results nvarchar(max) AS JSON
) j1
outer APPLY OPENJSON(j1.results)
WITH (
results_type nvarchar(100) '$.type',
results_id nvarchar(100) '$.id',
segmentation nvarchar(max) '$.tables.segmentation' AS JSON,
financial_statement_restate_json nvarchar(max) '$.tables.financial_statements_restate' AS JSON
) j2
outer APPLY OPENJSON(j2.segmentation)
WITH (
[period_3m_currency_id] nvarchar(max) '$.period_3m.currency_id',
[period_3m_fiscal_year_end] nvarchar(max) '$.period_3m.fiscal_year_end',
[period_3m_period] nvarchar(max) '$.period_3m.period',
[period_3m_report_type] nvarchar(max) '$.period_3m.report_type',
[period_3m_accounts_payable] nvarchar(max) '$.period_3m.accounts_payable',
[period_3m_accounts_receivable] nvarchar(max) '$.period_3m.accounts_receivable'
)
j3