CODEWITHSUNDEEP
jsonsql-server
Peter Dowdy
Peter Dowdy

Reputation: 439

Bracket notation for SQL Server json_value?

This works:

select json_value('{ "a": "b" }', '$.a')

This doesn't work:

select json_value('{ "a": "b" }', '$["a"]')

and neither does this:

select json_value('{ "a": "b" }', '$[''a'']')

In JSON, these are the same:

foo = { "a": "b" }
console.log(foo.a)
console.log(foo["a"])

What am I missing? I get an error trying to use bracket notation in SQL Server:

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

Upvotes: 0

Views: 1838

Answers (2)

Robert Hollon
Robert Hollon

Reputation: 76

MsSql reserves this for array index. SQL parses all JSON as a string literal, instead of as an object(JSON or ARRAY) with any hidden key.

Some of what SQL can do will vary with version. Here's a crash course on the annoying (but also really powerful, and fast once in place) requirements. I'm posting more than you need because a lot of the documentation for JSON through MsSql is lacking, and doesn't do justice to how strong it is with JSON.

MsDoc here: https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

In this example, we are working with a JSON "object" to separate the data into columns. Note how calling a position inside of an array is weird.

    declare @data nvarchar(max) = N'[{"a":"b","c":[{"some":"random","array":"value"},{"another":"random","array":"value"}]},{"e":"f","c":[{"some":"random","array":"value"},{"another":"random","array":"value"}]}]'

--make sure SQL is happy. It will not accept partial snippets    
select ISJSON(@data)
 
 
--let's look at the data in tabular form
select
json1.*
, json2.*
from openjson(@data)
    with    (
            a varchar --note there is no "path" specified here, as "a" is a key in the first layer of the object
            , c nvarchar(max) as JSON --must use "nvarchar(max)" and "as JSON" or SQL freaks out
            , c0 nvarchar(max) N'$.c[0]' as JSON
            ) as json1
cross apply openjson(json1.c) as json2

You can also pull out the individual values, if needed

select oj.value from openjson(@data) as oj where oj.[key] = 1;

select
    oj.value
    , JSON_VALUE(oj.value,N'$.e')
    , JSON_VALUE(oj.value,N'$.c[0].some')
    , JSON_VALUE(@data,N'$[1].c[0].some')       --Similar to your first example, but uses index position instead of key value. Works because SQL views the "[]" brackets as an array while trying to parse.
from openjson(@data) as oj
where oj.[key] = 1

Upvotes: 1

Peter Dowdy
Peter Dowdy

Reputation: 439

No sooner do I ask, than I stumble on an answer. I couldn't find this in any documentation anywhere, but select json_value('{ "a": "b" }', '$."a"') works. Bracket notation is not supported, but otherwise invalid keys can be escaped with quotation marks, e.g. select json_value('{ "I-m so invalid][": "b" }', '$."I-m so invalid]["') when in JavaScript that would be foo["I-m so invalid]["]

Upvotes: 3

Related Questions