Querying JSON column with nested arrays

arden
February 22, 2023
256 views
0 votes
2 Answers

Below is my JSON and query I’ve built so far, I feel like I’m just missing some silly piece. These data files are loaded as-is into SQL Server tables, so this JSON exists in one column.

Ultimately the nested array comes back null and it’s subsequent fields come back null.

Would like to see how to alter my query to pull the "plans" as its array and alternatively how to pull the individual fields from it

JSON Example

{"carrierPlans": [{"carrierId": 90, "carrierName": "Community Care", "plans": [{"planId": 170, "planName": "Silver", "pbpId": "H167"}]}]}

Query

-- carrierplans
SELECT  
c.[carrierId],
c.[carrierName],
c.[pbpId],
c.[planName],
c.[planId]
FROM [MyDatabase].[dbo].[LoadTable] b
    OUTER APPLY OPENJSON (b.[array]) -- array is the json column
WITH (
[carrierId] varchar(50),
[carrierName] varchar(50),
[pbpId] varchar(50),
[planName] varchar(50),
[planId] varchar(50)
) c
WHERE [key] = 'carrierplans'

OR if I wanted to capture the plans array something like this

SELECT  
c.[carrierId],
c.[carrierName],
c.[plans]
FROM [MyDatabase].[dbo].[LoadTable] b
    OUTER APPLY OPENJSON (b.[array]) -- array is the json column
WITH (
[carrierId] varchar(50),
[carrierName] varchar(50),
[plans] varchar(50)
) c
WHERE [key] = 'carrierplans'

Current Result

carrierId   carrierName pbpId   planName    planId
90          Community Care  NULL    NULL            NULL

Answers

- gbjbaanb
- February 22, 2023 at 2:22 am
- 0 votes
0
I found this impossible to do, the JSON support in SQL server is good, but limited.

I changed to using XML instead, they have selective queries that are very fully featured and this let me query inside nested blocks, and index on parts of the xml document.

you end up with queries like this:
```
SELECT xnodes.vals.value('.', 'int') AS [Carrier] FROM [LoadTable]
  CROSS APPLY MyXMLColumn.nodes('/carrierPlans') AS xnodes(vals) 
WHERE xnodes.vals.value('./@carrierID', 'int') = '90'
```
this link was very useful for the concepts, and this one for creating indexes – selective indexing is where the power is, and its really good.

The disadvantage is you have to create XML documents instead of JSON, and maybe MS will add the same kind of selective indexing power to JSON queries as we have in XML.
Login or Signup to reply.

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, j NVARCHAR(MAX));
INSERT @tbl (j) VALUES
(N'{
    "carrierPlans": [
        {
            "carrierId": 90,
            "carrierName": "Community Care",
            "plans": [
                {
                    "planId": 170,
                    "planName": "Silver",
                    "pbpId": "H167"
                }
            ]
        }
    ]
}');
-- DDL and sample data population, end

-- Method #1
SELECT carrierId = JSON_VALUE(a.Value,'$.carrierId')
    , carrierName = JSON_VALUE(a.Value,'$.carrierName')
    , c.*
FROM @tbl AS t
    OUTER APPLY OPENJSON (j, '$.carrierPlans') AS a
    CROSS APPLY OPENJSON (A.value, '$.plans') 
WITH (
    [planId] INT'$.planId',
    [carrierName] varchar(50) '$.planName',
    [pbpId] varchar(50) '$.pbpId'
) as c;

-- Method #2
SELECT carrierId = JSON_VALUE(a.Value,'$.carrierId')
    , carrierName = JSON_VALUE(a.Value,'$.carrierName')
    , plans = b.value
FROM @tbl AS t
    OUTER APPLY OPENJSON (j, '$.carrierPlans') AS a
    CROSS APPLY OPENJSON (A.Value, '$.plans') AS b;

-- Method #3
SELECT carrierId = JSON_VALUE(a.Value,'$.carrierId')
    , carrierName = JSON_VALUE(a.Value,'$.carrierName')
    , plans = JSON_QUERY(a.Value,'$.plans')
FROM @tbl AS t
    OUTER APPLY OPENJSON (j, '$.carrierPlans') AS a;

Output #1

carrierId	carrierName	planId	carrierName	pbpId
90	Community Care	170	Silver	H167

Output #2

carrierId	carrierName	plans
90	Community Care	{"planId": 170,"planName": "Silver","pbpId": "H167"}

Please signup or login to give your own answer.

Click here to cancel reply.