Equivalent of the $[*] jsonpath for SQL Server?

i86rm4rw 于 2023-08-02 发布在 SQL Server

关注(0)|答案(2)|浏览(127)

I would like to use a JSON path like $[*].name or $[..].name on a JSON like this one:

[
    {"id": 1, "name": "John"}, 
    {"id": 2, "name": "Mary"}, 
    {"id": 3, "name": "Peter"}
]

To get as result:

["John", "Mary", "Peter"]

But when I try this on SQL Server2019 using:

SELECT JSON_VALUE(json_data, '$[*].name')
FROM users

I got this error:

JSON path is not properly formatted. Unexpected character '*' is found at position 2.

So how can I get the expected result using a jsonpath compatible with SQL Server?

sql-server

来源：https://stackoverflow.com/questions/76717611/equivalent-of-the-jsonpath-for-sql-server

2条答案

按热度按时间

hgncfbus1#

Something like this.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, json_data NVARCHAR(MAX));
INSERT @tbl (json_data) VALUES 
('[{"id": 1, "name": "John"}, {"id": 2, "name": "Mary"}, {"id": 3, "name": "Peter"}]'),
('[{"id": 1, "name": "Sunny"}, {"id": 2, "name": "Paul"}, {"id": 3, "name": "Michael"}]');
-- DDL and sample data population, end

SELECT ID, result = '[' + STRING_AGG(QUOTENAME(JSON_VALUE(j.value, '$.name'),CHAR(34)), ',') + ']'
FROM @tbl
CROSS APPLY OPENJSON(json_data, '$') AS j
GROUP BY ID;

Output

ID	result
1	["John","Mary","Peter"]
2	["Sunny","Paul","Michael"]

赞(0）回复(0）举报 2023-08-02

quhf5bfb2#

A somewhat clearer and more accurate method

Use a schema for OPENJSON to immediately parse out properties into columns.
Use STRING_ESCAPE rather than QUOTENAME for correct quoting.

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, json_data NVARCHAR(MAX));
INSERT @tbl (json_data) VALUES 
('[{"id": 1, "name": "John"}, {"id": 2, "name": "Mary"}, {"id": 3, "name": "Peter"}]'),
('[{"id": 1, "name": "Sunny"}, {"id": 2, "name": "Paul"}, {"id": 3, "name": "Michael"}]');

SELECT
  ID,
  result = '[' + STRING_AGG('"' + STRING_ESCAPE(j.name, 'json') + '"', ',') + ']'
FROM @tbl
CROSS APPLY OPENJSON(json_data)
  WITH (
    name nvarchar(100)
  ) AS j
GROUP BY ID;

db<>fiddle

赞(0）回复(0）举报 2023-08-02

我来回答

Equivalent of the $[*] jsonpath for SQL Server?

2条答案

相关问题

热门标签

最新问答