SQL Server SQL JSON_QUERY constructing json path using values from a column and receiving error

pxiryf3j 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(165)

For the following query, I receive the error when running it:
The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.

SELECT JSON_QUERY(sr.Options, CONCAT('$[', sr.OptionId,']')
FROM Survey as sr

The column 'Options' contains JSON similar to this: [{"Id":1, "Text": "Yes"},{"Id":2, "Text": "No"}] and 'OptionId' relates to the index of one of these options which I want to return, but is nullable and in that case, I don't care what it returns. 'Options' will also sometimes be an empty array '[]' but in that case 'OptionId' is always NULL.

This is being run against SQL Server 13.0.4001

I attempted various things on argument 2 of JSON_QUERY such as using a case statement to only use sr.OptionId when it is not null, trying to convert the type, and a where statement on the entire query to filter out null.

sql-server

来源：https://stackoverflow.com/questions/77146178/sql-json-query-constructing-json-path-using-values-from-a-column-and-receiving-e

1条答案

按热度按时间

prdp8dxp1#

You may try a different statement using OPENJSON() with default schema and an additional APPLY operator. The result from the OPENJSON() call is a table with columns key , value and type . In your case the key column contains the index of the element in the specified JSON array and the value column contains a JSON object.

Sample data:

SELECT *
INTO Survey
FROM (VALUES
   (0, '[{"Id":1,"Text":"Yes"},{"Id":2,"Text":"No"}]'), 
   (1, NULL),
   (NULL, '[]') 
) v (OptionId, Options)

Statement:

SELECT *
FROM Survey sr
OUTER APPLY (
   SELECT [value] AS OptionJson
   FROM OPENJSON(sr.Options)
   WHERE sr.OptionID = CONVERT(int, [key])
) oa

As an additional note, as @AlwaysLearning already commented, the reason for the error is that using a variable as the value of path in the JSON_QUERY() is supported in SQL Server 2017+. For these versions, you may use the following statement:

SELECT 
   sr.OptionId,
   sr.Options,
   CASE 
      WHEN sr.OptionId IS NOT NULL THEN JSON_QUERY(sr.Options, CONCAT('$[', sr.OptionId, ']')) 
   END AS OptionJson
FROM Survey sr

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SQL JSON_QUERY constructing json path using values from a column and receiving error

1条答案

相关问题

热门标签

最新问答