Detect keys whose values are objects or arrays automatically in SQL JSON Query

2 votes
1 answer
972 views
                          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
    }
    }

I would like to **query all applications** and get the **keys with objects as values, automatically**. 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:

    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;

but if I add a new key-value pair where the value is either an object or array, I'd have to add another query as well.

How do I do it **automatically** and catching DataReceived values that are **not objects**?
                        
Asked by Real Quick (135 rep)
Jun 13, 2023, 11:05 PM
Last activity: Jun 13, 2023, 11:14 PM
Detect keys whose values are objects or arrays automatically in SQL JSON Query

Related Questions
