如何在SQL Server 2019中用OPENJSON提取全部JSON元素数据？

首先你的JSON格式存在小问题：当前的JSON是两个独立对象用逗号分隔，不是合法的JSON数组，需要在外层加上[]，否则SQL Server无法正确识别多个元素。修正后的JSON变量定义如下：

DECLARE @json nvarchar(max) = '
[
 {
        "id": 1,
        "name": "Buzz",
        "tagline": "A Real Bitter Experience.",
        "first_brewed": "09/2007",
        "description": "A light, crisp and bitter IPA brewed with English and American hops. A small batch brewed only once.",
        "image_url": "https://images.punkapi.com/v2/keg.png",
        "abv": 4.5,
        "ibu": 60,
        "target_fg": 1010,
        "target_og": 1044,
        "ebc": 20,
        "srm": 10,
        "ph": 4.4,
        "attenuation_level": 75,
        "volume": {
            "value": 20,
            "unit": "litres"
        },
        "boil_volume": {
            "value": 25,
            "unit": "litres"
        },
        "method": {
            "mash_temp": [
                {
                    "temp": {
                        "value": 64,
                        "unit": "celsius"
                    },
                    "duration": 75
                }
            ],
            "fermentation": {
                "temp": {
                    "value": 19,
                    "unit": "celsius"
                }
            },
            "twist": null
        },
        "ingredients": {
            "malt": [
                {
                    "name": "Maris Otter Extra Pale",
                    "amount": {
                        "value": 3.3,
                        "unit": "kilograms"
                    }
                },
                {
                    "name": "Caramalt",
                    "amount": {
                        "value": 0.2,
                        "unit": "kilograms"
                    }
                },
                {
                    "name": "Munich",
                    "amount": {
                        "value": 0.4,
                        "unit": "kilograms"
                    }
                }
            ],
            "hops": [
                {
                    "name": "Fuggles",
                    "amount": {
                        "value": 25,
                        "unit": "grams"
                    },
                    "add": "start",
                    "attribute": "bitter"
                },
                {
                    "name": "First Gold",
                    "amount": {
                        "value": 25,
                        "unit": "grams"
                    },
                    "add": "start",
                    "attribute": "bitter"
                },
                {
                    "name": "Fuggles",
                    "amount": {
                        "value": 37.5,
                        "unit": "grams"
                    },
                    "add": "middle",
                    "attribute": "flavour"
                },
                {
                    "name": "First Gold",
                    "amount": {
                        "value": 37.5,
                        "unit": "grams"
                    },
                    "add": "middle",
                    "attribute": "flavour"
                },
                {
                    "name": "Cascade",
                    "amount": {
                        "value": 37.5,
                        "unit": "grams"
                    },
                    "add": "end",
                    "attribute": "flavour"
                }
            ]
         }
},
{
        "id": 2,
        "name": "Trashy Blonde",
        "tagline": "You Know You Shouldnt",
        "first_brewed": "04/2008",
        "description": "A titillating, neurotic, peroxide punk of a Pale Ale. Combining attitude, style, substance, and a little bit of low self esteem for good measure; what would your mother say? The seductive lure of the sassy passion fruit hop proves too much to resist. All that is even before we get onto the fact that there are no additives, preservatives, pasteurization or strings attached. All wrapped up with the customary BrewDog bite and imaginative twist.",
        "image_url": "https://images.punkapi.com/v2/2.png",
        "abv": 4.1,
        "ibu": 41.5,
        "target_fg": 1010,
        "target_og": 1041.7,
        "ebc": 15,
        "srm": 15,
        "ph": 4.4,
        "attenuation_level": 76,
        "volume": {
            "value": 20,
            "unit": "litres"
        },
        "boil_volume": {
            "value": 25,
            "unit": "litres"
        },
        "method": {
            "mash_temp": [
                {
                    "temp": {
                        "value": 69,
                        "unit": "celsius"
                    },
                    "duration": null
                }
            ],
            "fermentation": {
                "temp": {
                    "value": 18,
                    "unit": "celsius"
                }
            },
            "twist": null
        },
        "ingredients": {
            "malt": [
                {
                    "name": "Maris Otter Extra Pale",
                    "amount": {
                        "value": 3.25,
                        "unit": "kilograms"
                    }
                },
                {
                    "name": "Caramalt",
                    "amount": {
                        "value": 0.2,
                        "unit": "kilograms"
                    }
                },
                {
                    "name": "Munich",
                    "amount": {
                        "value": 0.4,
                        "unit": "kilograms"
                    }
                }
            ],
            "hops": [
                {
                    "name": "Amarillo",
                    "amount": {
                        "value": 13.8,
                        "unit": "grams"
                    },
                    "add": "start",
                    "attribute": "bitter"
                },
                {
                    "name": "Simcoe",
                    "amount": {
                        "value": 13.8,
                        "unit": "grams"
                    },
                    "add": "start",
                    "attribute": "bitter"
                },
                {
                    "name": "Amarillo",
                    "amount": {
                        "value": 26.3,
                        "unit": "grams"
                    },
                    "add": "end",
                    "attribute": "flavour"
                },
                {
                    "name": "Motueka",
                    "amount": {
                        "value": 18.8,
                        "unit": "grams"
                    },
                    "add": "end",
                    "attribute": "flavour"
                }
            ],
            "yeast": "Wyeast 1056 - American Ale™"
        },
        "food_pairing": [
            "Fresh crab with lemon",
            "Garlic butter dipping sauce",
            "Goats cheese salad",
            "Creamy lemon bar doused in powdered sugar"
        ],
        "brewers_tips": "Be careful not to collect too much wort from the mash. Once the sugars are all washed out there are some very unpleasant grainy tasting compounds that can be extracted into the wort.",
        "contributed_by": "Sam Mason <samjbmason>"
}
]';

接下来修改查询语句，核心是先遍历外层的啤酒对象数组，再逐个解析每个对象的ingredients.malt数据：

SELECT 
    beer.id,
    beer.name AS beer_name,
    malt.name AS malt_name,
    amount.value AS amount_value,
    amount.unit AS amount_unit
FROM OPENJSON(@json, '$')
WITH (
    id int '$.id',
    name nvarchar(100) '$.name',
    malt_array nvarchar(max) '$.ingredients.malt' AS JSON
) AS beer
CROSS APPLY OPENJSON(beer.malt_array, '$')
WITH (
    name nvarchar(30) '$.name',
    amount nvarchar(max) '$.amount' AS JSON
) AS malt
CROSS APPLY OPENJSON(malt.amount, '$')
WITH (
    value decimal(5,2) '$.value',
    unit nvarchar(50) '$.unit'
) AS amount;

关键修改点说明： #

1. 遍历外层数组：通过OPENJSON(@json, '$')遍历整个啤酒对象数组，获取每个啤酒的基础信息，并将ingredients.malt作为JSON数组字段malt_array保留。
2. 关联每个啤酒的malt数据：使用CROSS APPLY OPENJSON(beer.malt_array, '$')展开每个啤酒对应的malt数组，得到每个malt条目。
3. 解析amount字段：最后再通过CROSS APPLY解析每个malt条目中的amount对象，提取数值和单位。

这样修改后，就能获取所有啤酒元素对应的所有malt数据，同时还能关联啤酒的id和名称，方便区分不同啤酒的原料信息。

内容的提问来源于stack exchange，提问作者Sven Marenković