How to use JsonPath expression with wildcards in MS SQL 2019's Json_Value?

Question

In my SQL Table, I have a column storing JSON with a structure similar to the following:

{
    "type": "Common",
    "items": [
        {
            "name": "landline",
            "number": "0123-4567-8888"
        },
        {
            "name": "home",
            "number": "0123-4567-8910"
        },
        {
            "name": "mobile",
            "number": "0123-4567-9910"
        }
    ]
}

This is the table structure I am using:

CREATE TABLE StoreDp(
[JsonData] [nvarchar](max), 
[Type] AS (json_value([JsonData],'lax $.type')) PERSISTED, 
[Items]  AS (json_value([JsonData],N'lax $.items[*].name')) PERSISTED
)

Now, when I am trying to insert the sample JSON (serialized) in the table column [JsonData], I am getting an error

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

I was expecting data to be inserted with value in [Items] as "[landline, home, mobile]"

I have validated the jsonpath expression, and it works fine except for in SQL Server.

Update: Corrected the SQL server version.

Answer 1

SQL Server cannot do shred and rebuild JSON using wildcard paths and JSON_VALUE.

You would have to use a combination of OPENJSON and STRING_AGG, and also STRING_ESCAPE if you want the result to be valid JSON.

SELECT
  (
    SELECT '[' + STRING_AGG('"' + STRING_ESCAPE(j.name, 'json') + '"', ',') + ']'
    FROM OPENJSON(sd.JsonData, '$.items')
      WITH (
        name varchar(20)
      ) j
  )
FROM StoreDp sd;

db<>fiddle

You could only do this in a computed column by using a scalar UDF. However those have major performance implications and should generally be avoided. I suggest you just make a view instead.