Querying JSON key with values as JSON in SQL Server

Question

I have these JSON:

Application 1:
{
"business_industry": "Agriculture",
"docs": [
 {
 "internal": false,
 "type": "Asset & Liability Statement"
 },
 {
 "internal": false,
 "name": "Privacy Consent",
 "type": "Privacy Consent"
 }
],
"quote": {
 "principal": 0,
 "short_id": "3856545"
}
}
Application 2:
{
"business_industry": "Construction",
"docs": [
 {
 "internal": false,
 "name": "Privacy Consent",
 "type": "Privacy Consent"
 }
],
"asset": {
 "model": null,
 "make": "3856545"
}
}
Application 3:
{
"business_industry": "Coal Mining",
"business_business_names": [
 {
 "business_organisation_name": "Centron Consulting Services",
 "business_effective_from": "2018年04月11日"
 }
],
"lite_doc": {
 "total_sales": 0,
 "total_sales2": 0,
 "refunds_present": false,
 "payment_arrangement_evident": false
}
}

which are derived from DataReceived in my Application model.

I would like to query all applications and get the keys with objects as values. Because I will use those keys as references for creating new models in another database.

Something like:

+----------+-----------+-------+----------------------------+-----------------------------+
| docs | quotes | asset | business_business_names | lite_doc |
+----------+-----------+-------+----------------------------+-----------------------------+
| internal | principal | model | business_organisation_name | total_sales |
| type | short_id | make | business_effective_from | total_sales2 |
| name | | | | refunds_present |
| | | | | payment_arrangement_evident |
+----------+-----------+-------+----------------------------+-----------------------------+

I will then create five models: docs, quotes, asset, business_business_names, and lite_doc which have properties listed above. The objects can either be another dictionary or an array.

This code is currently what I have:

BEGIN TRANSACTION;
BEGIN TRY
SELECT
 asset.[key] AS asset
FROM dbo.Application
-- SELECT ALL KEYS THAT HAVE OBJECTS AS VALUES AND DISPLAY THOSE OBJECTS' KEYS
CROSS APPLY OPENJSON(DataReceived, '$.asset') AS asset;
END TRY
BEGIN CATCH
-- Do nothing 
END CATCH
ROLLBACK TRANSACTION;

But it can only query the asset and it's not able to do DISTINCT.

enter image description here

For some reason DISTINCT does not work in the query.

enter image description here

• Charlieface: In future please do not post images of code or data, please only paste text.

Answer 1

You can use the following query

WITH docs AS (
 SELECT docs = STRING_AGG(j.[key], '
')
 FROM (
 SELECT DISTINCT j.[key]
 FROM Application a1
 CROSS APPLY OPENJSON(a1.DataReceived, '$.docs') j0
 CROSS APPLY OPENJSON(j0.value) j
 ) j
),
quotes AS (
 SELECT quotes = STRING_AGG(j.[key], '
')
 FROM (
 SELECT DISTINCT j.[key]
 FROM Application a1
 CROSS APPLY OPENJSON(a1.DataReceived, '$.quote') j
 ) j
),
asset AS (
 SELECT asset = STRING_AGG(j.[key], '
')
 FROM (
 SELECT DISTINCT j.[key]
 FROM Application a1
 CROSS APPLY OPENJSON(a1.DataReceived, '$.asset') j
 ) j
),
business_business_names AS (
 SELECT business_business_names = STRING_AGG(j.[key], '
')
 FROM (
 SELECT DISTINCT j.[key]
 FROM Application a1
 CROSS APPLY OPENJSON(a1.DataReceived, '$.business_business_names') j0
 CROSS APPLY OPENJSON(j0.value) j
 ) j
),
lite_doc AS (
 SELECT lite_doc = STRING_AGG(j.[key], '
')
 FROM (
 SELECT DISTINCT j.[key]
 FROM Application a1
 CROSS APPLY OPENJSON(a1.DataReceived, '$.lite_doc') j
 ) j
)
SELECT *
FROM docs
CROSS JOIN quotes
CROSS JOIN asset
CROSS JOIN business_business_names
CROSS JOIN lite_doc;

Each CTE selects the distinct key names. Values which are arrays need to be broken out a second time using OPENJSON.

And STRING_AGG does not support DISTINCT so you need a subquery for that. Otherwise you could have done STRING_AGG(DISTINCT j.[key].

Finally you just cross-join everything to get it as one table.

db<>fiddle

• Real Quick: This worked, but is there a way to detect keys whose values are objects or arrays automatically?
• Charlieface: You could use a more generic approach such as this dbfiddle.uk/LC_iTeE5 I suggest you make a new question for this, as the question as presented is answered.
• Real Quick: this is perfect. I will post tomorrow once I get to work. Thank you!
• Real Quick: Could you also post the answer here? stackoverflow.com/q/76411130/12415287
• Charlieface: No you're not supposed to cross-post. You should get a mod to merge one or the other in.