1

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

1
  • In future please do not post images of code or data, please only paste text. Commented Jun 13, 2023 at 13:33

1 Answer 1

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

6
  • This worked, but is there a way to detect keys whose values are objects or arrays automatically? Commented Jun 13, 2023 at 12:46
  • 1
    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. Commented Jun 13, 2023 at 13:33
  • this is perfect. I will post tomorrow once I get to work. Thank you! Commented Jun 13, 2023 at 15:04
  • Could you also post the answer here? stackoverflow.com/q/76411130/12415287 Commented Jun 13, 2023 at 20:17
  • No you're not supposed to cross-post. You should get a mod to merge one or the other in. Commented Jun 13, 2023 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.