Cannot access keys by index with OPENJSON

Question

In SQL, how do I extract the value of a key by index [0]? OR, what is the best way to extract a value from this JSON structure?

declare @jsonString nvarchar(max)

set @jsonString = '{
"action": "edit",
"data": {
    "Accountable Inventory Technician|99999": {
        "PosTitle": "Accountable Inventory TECH",
        "Publish": "true"
    }
}
}'

This will get me the name of the keys, "action" and "data":

select [key]
from OPENJSON(@jsonString, '$')

This will get me the value of the first key under Data "Accountable Inventory Technician...":

select *
from OPENJSON(@jsonString, '$.data')

I cannot get the value of "action". This returns nothing:

select *
from OPENJSON(@jsonString, 'lax $.action')

I cannot reference by index. This returns nothing :

select *
from OPENJSON(@jsonString, '$.data[0].PosTitle')

What am I doing wrong?

Answer 1

In SQL, how do I extract the value of a key by index [0]? OR, what is the best way to extract a value from this JSON structure?

JSON index is used for arrays. For example, if your JSON has ["some","values","in","an","array"] then referencing the [0] item will have the "some" value. Your example doesn't have an array so you won't be able to access it that way.

I cannot get the value of "action".

Using JSON_VALUE you can access specific items in your object:

SELECT JSON_VALUE(@jsonString, '$.action') [action];
SELECT JSON_VALUE(@jsonString, '$.data."Accountable Inventory Technician|99999".PosTitle') PosTitle;

If your JSON is stored in a table, you can access it with OPENJSON and CROSS APPLY

DROP TABLE IF EXISTS #json 
CREATE TABLE #json (JsonString NVARCHAR(MAX));

INSERT #json SELECT '{
    "action": "edit",
    "data": {
        "Accountable Inventory Technician|99999": {
            "PosTitle": "Accountable Inventory TECH",
            "Publish": "true"
        }
    }
    }'

SELECT * FROM #json j
CROSS APPLY
OPENJSON(j.JsonString)
WITH
    (
        [Action] varchar(255) '$.action',
        PosTitle varchar(255) '$.data."Accountable Inventory Technician|99999".PosTitle'
    );