Microsoft SQL Server
Zoekopdrachten met JSON-gegevens

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.