Sql Query Json Array items by Value-CodePudding

I have searched and can't seem to find somewhere doing exactly what I am trying. I have a json similar to as follows in multiple rows in my database:

    {
    "date": "0001-01-01T00:00:00",
    "details": {
        "detail": [
            {
                "item": "11",
                "value": "xt"
            },
            {
                "item": "12",
                "value": "xy"
            },
            {
                "item": "13",
                "value": "xz"
            },
            {
                "item": "14",
                "value": "zz"
            }
        ]
    }
}

I want to do sql that does this:

select ID
       jsonColumn.value where item=11 as X
       jsonColumn.value where item=12 as Y
from tbl

So I have results like this

----------------------
|ID  |X        |Y    |
----------------------
|1   |xt       |xy   |
----------------------

I have tried using JSONVALUE but I seem to need to do it by the array item number like this:

  '$.details.detail[3].value'

which doesn't really work

I have also tried this:

    SELECT id, x.item, x.value
    FROM 
    tbl F
    CROSS APPLY (select *
            FROM  OPENJSON(F.Json,'$.details.detail')
            CROSS APPLY OPENJSON(value)
                WITH (item NVARCHAR(25) '$.item',
                      value NVARCHAR(max) '$.value') As x
     where F.ID=55

Which I can use to print out all the items and values but then I'd have to query each separately again.

Is there a way of combining the two in to one big query that won't be completely inefficient?

CodePudding user response：

Seems what you want is a pivot. I personally use conditional aggregation over the far more restrictive PIVOT operator. The JSON you supplied was invalid, so I took some liberties correcting it in my sandbox environment:

SELECT --ID,
       MAX(CASE d.item WHEN 11 THEN d.[value] END) AS X,
       MAX(CASE d.item WHEN 12 THEN d.[value] END) AS Y
FROM (VALUES(@JSON))V(J) --Your Table
     CROSS APPLY OPENJSON(V.J,'$.details') 
                 WITH (detail nvarchar(MAX) AS JSON ) OJ
     CROSS APPLY OPENJSON(OJ.detail)
                 WITH(item int,
                      [value] nvarchar(2)) d;

If you are using this against a table, and not limiting the data to a single row, you'll need to also add a GROUP BY clause on the relevant columns (ID?).