'How to use JsonPath expression with wildcards in MS SQL 2019's Json_Value?
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
