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.

For some reason DISTINCT does not work in the query.

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