0

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 Server 2019 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?

celsowm
  • 846
  • 9
  • 34
  • 59
  • Well, by itself, `["John", "Mary", "Peter"]` isn't valid JSON, so if that's the result you're actually wanting to might want to use other SQL Server functionality like `STRING_AGG` instead. – AlwaysLearning Jul 19 '23 at 03:07
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Jul 19 '23 at 03:24
  • There is a link to fiddle with ddl – celsowm Jul 19 '23 at 03:43
  • 1
    @AlwaysLearning Perfectly valid to have a bare array, not sure what you mean. – Charlieface Jul 19 '23 at 09:45

2 Answers2

2

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"]
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
1

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43