Querying JSON key with values as JSON in SQL Server

1 vote
1 answer
1097 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
    }
    }

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.



                        
Asked by Real Quick (135 rep)
Jun 13, 2023, 01:48 AM
Last activity: Jun 13, 2023, 11:31 AM
Querying JSON key with values as JSON in SQL Server

Related Questions
