Sven Marenković
Reputation: 1
How to get results in OPENJSON from whole json?
I have a JSON structure which has several elements with their respective values. Each element has method.mash_temp, ingredients.malt and ingredients.hops, all of which can has multiple values. How would I query it so I get values from each element? (there's 25 elements, in this example is only 2 for simplification)
I use sql server 2019 P.S. I'm not even sure if I'm using "elements" in te correct context, but I think you'll get what I'm trying to do
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>"
}';
Here's a query for ingredients.malt which works, but only gets data from the first element, how would I query it so I get all data?
SELECT name, amount_value, amount_unit
FROM OPENJSON(@json, '$.ingredients.malt')
WITH (
name nvarchar(30) '$.name'
, amount nvarchar(max) '$.amount' as JSON
)
CROSS APPLY OPENJSON(amount, '$')
WITH (
amount_value varchar(5) '$.value'
, amount_unit nvarchar(50) '$.unit'
)
;
Upvotes: 0
Views: 614
Answers (1)
Sander
Reputation: 4042
Opening the JSON on a higher level than the path $.ingredients.malt, will return other categories besides malt.
You can use the json_value() function to fetch values from a JSON string.
select category.[key] as CategoryName,
json_value(ingredient.[value], '$.name') as IngredientName,
json_value(ingredient.[value], '$.amount.value') as IngredientAmount,
json_value(ingredient.[value], '$.amount.unit') as IngredientUnit
from openjson(@json, '$.ingredients') category
cross apply openjson(category.[value], '$') ingredient
This gives me
CategoryName IngredientName IngredientAmount IngredientUnit
------------ ---------------------- ---------------- --------------
malt Maris Otter Extra Pale 3.3 kilograms
malt Caramalt 0.2 kilograms
malt Munich 0.4 kilograms
hops Fuggles 25 grams
hops First Gold 25 grams
hops Fuggles 37.5 grams
hops First Gold 37.5 grams
hops Cascade 37.5 grams
To get the data from the second object in the JSON ("id": 2), the invalid JSON from the question must be corrected to be valid first. It is missing the square array brackets [].
This turns the invalid JSON
{
"id": 1,
...
},
{
"id": 2,
...
}
into a valid JSON
[
{
"id": 1,
...
},
{
"id": 2,
...
}
]
To parse the malt data from all objects, you can then use the query below:
select json_value(item.[value], '$.name') as ItemName,
json_value(ingredient.[value], '$.name') as MaltName,
json_value(ingredient.[value], '$.amount.value') as MaltAmount,
json_value(ingredient.[value], '$.amount.unit') as MaltUnit
from openjson(@json2, '$') item
cross apply openjson(item.[value], '$.ingredients.malt') ingredient;
Resulting in
ItemName MaltName MaltAmount MaltUnit
------------- ---------------------- ---------- ---------
Buzz Maris Otter Extra Pale 3.3 kilograms
Buzz Caramalt 0.2 kilograms
Buzz Munich 0.4 kilograms
Trashy Blonde Maris Otter Extra Pale 3.25 kilograms
Trashy Blonde Caramalt 0.2 kilograms
Trashy Blonde Munich 0.4 kilograms
Upvotes: 2
Related Questions
- Return SQL Server database query with nested Json
- Use openjson to get columns from JSON with multiple arrays of elements
- SQL Server parse nested json using OPENJSON
- SQL Server OpenJson - retrieve row based on nested Json, querying multiple Json rows
- How to extract data from nested JSON in SQL Server
- SQL Server OPENJSON read nested json
- Get Nested JSON from SQL table
- How to get multiple columns of values from OPENJSON and cross apply
- OpenJson in SQL Server for nested json data?
- How do I use OPENJSON on the result of a sub OPENJSON