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

Answer 1

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 |
+-----------+--------+