Zoeken…


Waarden van JSON gebruiken in query

Met de JSON_VALUE-functie kunt u gegevens uit JSON-tekst nemen op het pad dat is opgegeven als het tweede argument, en deze waarde gebruiken in elk deel van de selectiequery:

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'

JSON-waarden gebruiken in rapporten

Nadat JSON-waarden uit JSON-tekst zijn geëxtraheerd, kunt u ze in elk deel van de query gebruiken. U kunt een soort rapport over JSON-gegevens maken met groeperingsaggregaties, enz:

select JSON_VALUE(Data, '$.Type') as type,
        AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost
from Product
group by JSON_VALUE(Data, '$.Type') 
having JSON_VALUE(Data, '$.Type') is not null

Filter slechte JSON-tekst uit queryresultaten

Als sommige JSON-tekst mogelijk niet correct is opgemaakt, kunt u die vermeldingen uit de query verwijderen met de ISJSON-functie.

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
and ISJSON(Data) > 0

Waarde bijwerken in JSON-kolom

JSON_MODIFY-functie kan worden gebruikt om de waarde op een bepaald pad bij te werken. U kunt deze functie gebruiken om de oorspronkelijke waarde van de JSON-cel in de UPDATE-instructie te wijzigen:

update Product
set Data = JSON_MODIFY(Data, '$.Price', 24.99)
where ProductID = 17;

De JSON_MODIFY-functie werkt de prijssleutel bij of maakt deze aan (als deze niet bestaat). Als de nieuwe waarde NULL is, wordt de sleutel verwijderd. De JSON_MODIFY-functie behandelt de nieuwe waarde als tekenreeks (speciale tekens omzeilen, dubbele aanhalingstekens om de juiste JSON-tekenreeks te maken). Als uw nieuwe waarde een JSON-fragment is, moet u dit verpakken met de JSON_QUERY-functie:

update Product
set Data = JSON_MODIFY(Data, '$.tags', JSON_QUERY('["promo","new"]'))
where ProductID = 17;

JSON_QUERY functie zonder tweede parameter gedraagt zich als een "cast naar JSON". Aangezien het resultaat van JSON_QUERY een geldig JSON-fragment (object of array) is, zal JSON_MODIFY niet ontsnappen aan deze waarde wanneer invoer JSON wordt gewijzigd.

Nieuwe waarde toevoegen aan JSON-array

JSON_MODIFY-functie kan worden gebruikt om een nieuwe waarde toe te voegen aan een array in JSON:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', "sales")
where ProductID = 17;

Nieuwe waarde wordt aan het einde van de array toegevoegd of er wordt een nieuwe array met waarde ["sales"] gemaakt. De JSON_MODIFY-functie behandelt de nieuwe waarde als tekenreeks (speciale tekens omzeilen, dubbele aanhalingstekens om de juiste JSON-tekenreeks te maken). Als uw nieuwe waarde een JSON-fragment is, moet u dit verpakken met de JSON_QUERY-functie:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', JSON_QUERY('{"type":"new"}'))
where ProductID = 17;

JSON_QUERY functie zonder tweede parameter gedraagt zich als een "cast naar JSON". Aangezien het resultaat van JSON_QUERY een geldig JSON-fragment (object of array) is, zal JSON_MODIFY niet ontsnappen aan deze waarde wanneer invoer JSON wordt gewijzigd.

JOIN tafel met inner JSON-collectie

Als u een 'onderliggende tabel' hebt die is opgemaakt als JSON-verzameling en in de rij is opgeslagen als JSON-kolom, kunt u deze verzameling uitpakken, transformeren naar tabel en samenvoegen met de bovenliggende rij. In plaats van de standaard JOIN-operator moet u CROSS APPLY gebruiken. In dit voorbeeld worden productonderdelen opgemaakt als verzameling JSON-objecten in en opgeslagen in de kolom Gegevens:

select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
    CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))

Het resultaat van de query is gelijk aan de join tussen product- en onderdelentabellen.

Rijen zoeken die waarde bevatten in de JSON-array

In dit voorbeeld kan de tags-array verschillende trefwoorden bevatten zoals ["promo", "sales"], dus we kunnen deze array openen en waarden filteren:

select ProductID, Name, Color, Size, Price, Quantity
from Product
    CROSS APPLY OPENJSON(Data, '$.Tags') 
where value = 'sales'

OPENJSON opent de binnenste verzameling tags en retourneert deze als tabel. Vervolgens kunnen we de resultaten filteren op een waarde in de tabel.



Modified text is an extract of the original Stack Overflow Documentation
Licentie onder CC BY-SA 3.0
Niet aangesloten bij Stack Overflow