Extract head of an array using open_json?

Question

I am have sample JSON as below

{
    "12345": {
        "percent": 26,
        "avgPrice": 32,
        "avgNewOfferCount": 12,
        "avgUsedOfferCount": -1,
        "isFBA": true,
        "lastSeen": 653
    }
}

I am getting the array values but since my array head i.e. '12345' is not fixed but an ID I am not able to get it and I can't hardcode since ID will change.

I am trying as below:

SELECT @json j;

INSERT INTO [test].[test]
    SELECT      
    JSON_VALUE(j.[value],$.j) AS key,
    JSON_VALUE(j.[value],'$.percent') AS stat    
FROM OPENJSON(@json) j;

But getting NULL in the key column.

That query would error, due to a syntax error, not return `NULL`. — Thom A, Jun 18 '23 at 17:51

Charlieface · Answer 1 · 2023-06-18T22:19:49.303

Your query would have worked by simply selecting j.[key].

SELECT      
    j.[key],
    JSON_VALUE(j.[value],'$.percent') AS stat    
FROM OPENJSON(@json) j;

But you are better off just using two levels of OPENJSON, one feeding into the next.

The first does not use a schema, so you get a list of key value pairs. key represents the property name, which in this case would be 12345. Then you push the value into the next call.

SELECT
  j1.[key],
  j2.*
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(j1.value)
  WITH (
    [percent] int,
    avgPrice decimal(9,2),
    avgNewOfferCount int,
    avgUsedOfferCount int,
    isFBA bit,
    lastSeen int
  ) j2;

db<>fiddle

Not clear why the downvote – John Cappelletti Jun 18 '23 at 23:09 — John Cappelletti, Jun 18 '23 at 23:09

Extract head of an array using open_json?

1 Answers1