Question 109630 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Querying JSON column with nested arrays

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
Tags:

2

Answers


  1. 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.
  2. 0

    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"}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search