Access JSON_VALUE from SQL Column containing JSON String with Arrays

Question

Looking to use JSON_VALUE to access some data within a JSON column in an SQL Server table.

I have a table (tblMyTable) that has the following columns and data;

| Column1  | JSONColumn     |
| -------- | -------------- |
| 1        | {some JSON}    |

Where {some JSON} looks as follows;


    [
       {
          "RoleName":"Client",
          "Roles":[
             {
                "ContactID":21568,
                "ContactName":"FullName1"
             },
             {
                "ContactID":31568,
                "ContactName":"FullName2"
             }
          ]
       },
       {
          "RoleName":"Owner",
          "Roles":[
             {
                "ContactID":1,
                "ContactName":"Billy Buxton"
             }
          ]
       }
    ]

I am wanting to use something like this to access the ContactName of Billy Buxton;


    SELECT JSON_VALUE(JSONColumn, '$[Owner].Roles[0].ContactName') AS ContactName
    FROM tblMyTable
    WHERE Column1=1

RESULT:

| ContactName |
| ----------- |
| Billy Buxton|

The issue is that I don't know where the Owner RoleName is in the JSON so I can't use; '$[0].Roles[0].ContactName' as it might be '$[1].Roles' or '$[10].Roles'.

Is there a way to do this by specifying the 'Owner' or 'Client' RoleName?

In addition, if I use the following I would get the array of ContactNames;


    SELECT JSON_QUERY(JSONColumn, '$[Client].Roles.ContactName') AS ContactNames
    FROM tblMyTable
    WHERE Column1=1

RESULT:

| ContactNames       |
| ------------------ |
| FullName1,FullName2|

I am able to change the structure of the JSON if needed, and would consider using OpenJSON if that works with specifying a path (eg; $[Owner].Roles[0].ContactName or similar)

I had come up with the following option, but it's using OPENJSON which is much messier than using JSON_VALUE or JSON_QUERY;


    SELECT Y.RoleName,STRING_AGG(Z.ContactName,',') AS ContactName
    FROM (
        SELECT  '[{"RoleName":"Client","Roles":[{"ContactID":21568,"ContactName":"FullName1"},{"ContactID":31568,"ContactName":"FullName2"}]},{"RoleName":"Owner","Roles":[{"ContactID":1,"ContactName":"Billy Buxton"}]}]' AS Roles
    )X
    CROSS APPLY OPENJSON (X.Roles) 
    WITH (
        RoleName VarChar(50),
        Roles nVarChar(MAX) AS JSON
        )Y
    CROSS APPLY OPENJSON (Y.Roles)
    WITH (
        ContactID   BigInt,
        ContactName VarChar(255)
    )Z
    WHERE Y.RoleName='Client'
    GROUP BY Y.RoleName

RESULT:

| RoleName | ContactName        |
| -------- | ------------------ |
| Client   | FullName1,FullName2|

Thanks in advance for any assistance you can provide.

Drilling in with `OPENJSON` is pretty much it for now. Unfortunately JSON queries are fairly limited. Hopefully they will be enhanced in future version of SQL Server... — squillman, Sep 21 '21 at 14:10

Ronen Ariely · Answer 1 · 2021-09-23T03:30:56.970

Check if this solve your needs

---------------- DDL+DML
use tempdb
GO

CREATE TABLE tblMyTable (ID INT, JSONColumn NVARCHAR(MAX))
GO

INSERT tblMyTable (ID, JSONColumn)
VALUES (1, '    [
       {
          "RoleName":"Client",
          "Roles":[
             {
                "ContactID":21568,
                "ContactName":"FullName1"
             },
             {
                "ContactID":31568,
                "ContactName":"FullName2"
             }
          ]
       },
       {
          "RoleName":"Owner",
          "Roles":[
             {
                "ContactID":1,
                "ContactName":"Billy Buxton"
             }
          ]
       }
    ]')
GO

---------- Solution
select ID, RoleName, Roles, Soluion = JSON_VALUE(Roles, '$[0].ContactName')
from tblMyTable t
CROSS APPLY OpenJson(t.JSONColumn)
WITH (
        RoleName VarChar(50),
        Roles nVarChar(MAX) AS JSON
        )Y
WHERE RoleName = 'Owner'
GO

Note! I assume that there is only one role for the Owner, which is why I can use $[0]

Access JSON_VALUE from SQL Column containing JSON String with Arrays

1 Answers1