TSQL JSON_QUERY can you use a filter in the JSON Path

Question

I have a table with a column that holds valid JSON, heres an example of the JSON structure:

{
    "Requirements": {
        "$values": [
            {
                "$type": "List",
                "ListId": "956cf9c5-24ab-47d9-8082-940118f2f1a3",
                "DefaultValue": "",
                "MultiSelect": true,
                "Selected": null,
                "MultiSelected": {
                    "$type": "ListItem",
                    "$values": [
                        "Value1",
                        "Value2",
                        "Value3"
                    ]
                }
            },
            {
                "$type": "List",
                "ListId": "D11149DD-A682-4BC7-A87D-567954779234",
                "DefaultValue": "",
                "MultiSelect": true,
                "Selected": null,
                "MultiSelected": {
                    "$type": "ListItem",
                    "$values": [
                        "Value4",
                        "Value5",
                        "Value6",
                        "Value7"
                    ]
                }
            }
        ]
    }
}

I need to return the values from MultiSelected collection depending on the value of ListID.

I'm using the following JSON Path to retun value

$.Requirements."$values"[?(@.ListId=='956cf9c5-24ab-47d9-8082-940118f2f1a3')].MultiSelected."$values"

This worked fine in a JSON Expression tester.

But when I try to use it to query the table I get the following error:

JSON path is not properly formatted. Unexpected character '?' is found at position 25.

The query I'm using is as follows:

SELECT  ID                                          AS PayloadID,
        Items.Item                                  AS ItemsValues
FROM    dbo.Payload
CROSS APPLY ( SELECT    *
                FROM    OPENJSON( JSON_QUERY( Payload, '$.Requirements."$values"[?(@.ListId==''956cf9c5-24ab-47d9-8082-940118f2f1a3'')].MultiSelected."$values"' ) )
                WITH ( Item nvarchar(200) '$' ) ) AS Items
WHERE   ID = 3

I've tried replacing

?(@.ListId==''956cf9c5-24ab-47d9-8082-940118f2f1a3'')

with 0 and it works fine on SQL Server.

My question is, is filter syntax ?(...) supported in JSON_QUERY or is there something else I should be doing?

The database is running on Azure, were the database compatability level is set to SQL Server 2017 (140).

Thanks for your help in advance.

Andy

Did you ever find a solution to this problem? Thanks Jol – jolySoft Jul 21 '21 at 08:44 — jolySoft, Jul 21 '21 at 08:44

score 1 · Accepted Answer · answered Jan 14 '20 at 14:13

I would use openjson twice in stead

drop table if exists #payload
create table #payload(ID int,Payload nvarchar(max))
insert into #payload VALUES
(3,N'
{
    "Requirements": {
        "$values": [
            {
                "$type": "List",
                "ListId": "956cf9c5-24ab-47d9-8082-940118f2f1a3",
                "DefaultValue": "",
                "MultiSelect": true,
                "Selected": null,
                "MultiSelected": {
                    "$type": "ListItem",
                    "$values": [
                        "Value1",
                        "Value2",
                        "Value3"
                    ]
                }
            },
            {
                "$type": "List",
                "ListId": "D11149DD-A682-4BC7-A87D-567954779234",
                "DefaultValue": "",
                "MultiSelect": true,
                "Selected": null,
                "MultiSelected": {
                    "$type": "ListItem",
                    "$values": [
                        "Value4",
                        "Value5",
                        "Value6",
                        "Value7"
                    ]
                }
            }
        ]
    }
}'
)

SELECT  ID                                          AS PayloadID,
Items.[value]
FROM    #Payload a
CROSS APPLY OPENJSON(  Payload, '$.Requirements."$values"' ) with ( ListId varchar(50),MultiSelected nvarchar(max) as json) b
CROSS APPLY OPENJSON(  MultiSelected,'$."$values"' )  Items
where 
   a.id=3
   AND b.listid='956cf9c5-24ab-47d9-8082-940118f2f1a3'

Reply:

+-----------+--------+
| PayloadID | value  |
+-----------+--------+
|         3 | Value1 |
|         3 | Value2 |
|         3 | Value3 |
+-----------+--------+

Appreciate the answer, But need to know filter syntax ?(...) supported in JSON_QUERY ? — jamir, Mar 18 '20 at 18:00

TSQL JSON_QUERY can you use a filter in the JSON Path

1 Answers1