0

Need to retrieve the values of Batters/Batter/Type 1-4

Here is the JSON data

[
{
        "id": "0001",
        "type": "donut",
        "name": "Cake",
        "ppu": 0.55,
        "batters":
            {
                "batter":
                    [
                        { "id": "1001", "type": "Regular" },
                        { "id": "1002", "type": "Chocolate" },
                        { "id": "1003", "type": "Blueberry" },
                        { "id": "1004", "type": "Devil's Food" }
                    ]
            },
        "topping":
            [
                { "id": "5001", "type": "None" },
                { "id": "5002", "type": "Glazed" },
                { "id": "5005", "type": "Sugar" },
                { "id": "5007", "type": "Powdered Sugar" },
                { "id": "5006", "type": "Chocolate with Sprinkles" },
                { "id": "5003", "type": "Chocolate" },
                { "id": "5004", "type": "Maple" }
            ]
    }
]

Used selected * from openjson to retrieve the values, but unable to get the nested/nested values

Select * from openjson(@json_known, '$[1]')
with
(
KeyID int '$.id',
[Type] varchar(max) '$.type',
[Name] varchar(max) '$.name',
PPU varchar(max) '$.ppu',
Batter0 varchar(max) '$.batters.batter.type[0]',
Batter1 varchar(max) '$.batters.batter.id[1]',
Batter2 varchar(max) '$.batters.batter.type[2]',
Batter3 varchar(max) '$.batters.batter.type[3]'
)
Dale K
  • 25,246
  • 15
  • 42
  • 71
Grim Rieber
  • 9
  • 1
  • 1
  • 7

1 Answers1

0

You could use:

Select x.Batter
from openjson(@json_known, '$') s
cross apply openjson(json_query(s.value, '$.batters.batter'))
with (id INT '$.id',Batter varchar(max) '$.type') x;

db<>fidlde demo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Thank you. This does give the information I require, but not in the format I need. My end result is to have a column for each batter with it's value. e.g. Batter1 = Regular, Batter2 = Chocolate, Batter3 = Blueberry, Batter4=Devil's food – Grim Rieber Jul 03 '19 at 18:22
  • @GrimRieber If you want proper format just post enitre result table for proposed JSON. If you spend a second you could easily PIVOT data by yourself – Lukasz Szozda Jul 03 '19 at 20:13