Veelvoorkomende problemen met JSON in SQL Server oplossen

Van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (serverless SQL pool only)SQL database in Microsoft Fabric

Hier vindt u antwoorden op enkele veelgestelde vragen over de ingebouwde JSON-ondersteuning in de SQL Database Engine.

VOOR JSON- en JSON-uitvoer

VOOR JSON-PAD of VOOR JSON AUTO?

Question. Ik wil een JSON-tekstresultaat maken op basis van een eenvoudige SQL-query in één tabel. VOOR JSON PATH en VOOR JSON AUTO produceren dezelfde uitvoer. Welke van deze twee opties moet ik gebruiken?

Answer. Gebruik FOR JSON PATH. Hoewel er geen verschil is in de JSON-uitvoer, past de AUTO-modus extra logica toe waarmee wordt gecontroleerd of kolommen moeten worden genest. Overweeg PATH de standaardoptie.

Een geneste JSON-structuur maken

Question. Ik wil complexe JSON produceren met verschillende matrices op hetzelfde niveau. VOOR JSON PATH kan met behulp van paden geneste objecten maken, en VOOR JSON AUTO maakt een extra nestniveau voor elke tabel. Met geen van deze twee opties kan ik de gewenste uitvoer genereren. Hoe kan ik een aangepaste JSON-indeling maken die niet rechtstreeks door de bestaande opties wordt ondersteund?

Answer. U kunt elke gegevensstructuur maken door FOR JSON-query's toe te voegen als kolomexpressies die JSON-tekst retourneren. U kunt JSON ook handmatig maken met behulp van de functie JSON_QUERY. In het volgende voorbeeld ziet u deze technieken.

SELECT col1, col2, col3,  
     (SELECT col11, col12, col13 FROM t11 WHERE t11.FK = t1.PK FOR JSON PATH) as t11,  
     (SELECT col21, col22, col23 FROM t21 WHERE t21.FK = t1.PK FOR JSON PATH) as t21,  
     (SELECT col31, col32, col33 FROM t31 WHERE t31.FK = t1.PK FOR JSON PATH) as t31,  
     JSON_QUERY('{"'+col4+'":"'+col5+'"}') as t41  
FROM t1  
FOR JSON PATH

Elk resultaat van een FOR JSON-query of de JSON_QUERY-functie in de kolomexpressies wordt opgemaakt als een afzonderlijk geneste JSON-subobject en opgenomen in het hoofdresultaat.

Dubbele escape-JSON voorkomen in FOR JSON-uitvoer

Question. Ik heb JSON-tekst opgeslagen in een tabelkolom. Ik wil deze opnemen in de uitvoer van FOR JSON. Maar FOR JSON vervangt alle tekens in de JSON, dus ik krijg een JSON-tekst in plaats van een genest object, zoals te zien in het volgende voorbeeld.

SELECT 'Text' AS myText, '{"day":23}' AS myJson  
FOR JSON PATH

Deze query produceert de volgende uitvoer.

[{"myText":"Text", "myJson":"{\"day\":23}"}]

Hoe kan ik dit gedrag voorkomen? Ik wil {"day":23} worden geretourneerd als een JSON-object en niet als escape-tekst.

Answer. JSON die is opgeslagen in een tekstkolom of een letterlijke tekst, wordt behandeld als elke tekst. Met andere woorden, het is omgeven door dubbele aanhalingstekens en geëscaped. Als u een niet-gescaped JSON-object wilt retourneren, geeft u de JSON-kolom als argument door aan de functie JSON_QUERY, zoals wordt weergegeven in het volgende voorbeeld.

SELECT col1, col2, col3, JSON_QUERY(jsoncol1) AS jsoncol1  
FROM tab1  
FOR JSON PATH

JSON_QUERY zonder de optionele tweede parameter retourneert alleen het eerste argument als resultaat. Aangezien JSON_QUERY altijd geldige JSON teruggeeft, weet FOR JSON dat dit resultaat niet hoeft te worden geëscaped.

JSON die is gegenereerd met de WITHOUT_ARRAY_WRAPPER-clausule, wordt ontsnapt in FOR JSON-uitvoer

Question. Ik probeer een kolomexpressie op te maken met behulp van FOR JSON en de optie WITHOUT_ARRAY_WRAPPER.

SELECT 'Text' as myText,  
   (SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as myJson  
FOR JSON PATH

Het lijkt erop dat de tekst die door de FOR JSON-query wordt geretourneerd, als platte tekst geëscapeerd is. Dit gebeurt alleen als WITHOUT_ARRAY_WRAPPER is opgegeven. Waarom wordt het niet als een JSON-object behandeld en niet ontsloten in het resultaat opgenomen?

Answer. Als u de WITHOUT_ARRAY_WRAPPER optie opgeeft in de binnenste FOR JSON, is de resulterende JSON-tekst niet noodzakelijkerwijs geldige JSON. Daarom gaat de buitenste FOR JSON ervan uit dat dit tekst zonder opmaak is en de tekenreeks escapet. Als u zeker weet dat de JSON-uitvoer geldig is, verpakt u deze met de JSON_QUERY-functie om deze te promoveren naar JSON met de juiste indeling, zoals wordt weergegeven in het volgende voorbeeld.

SELECT 'Text' as myText,  
      JSON_QUERY((SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as myJson  
FOR JSON PATH

OPENJSON- en JSON-invoer

Een geneste JSON-subobject retourneren uit JSON-tekst met OPENJSON

Question. Ik kan geen matrix met complexe JSON-objecten openen die zowel scalaire waarden, objecten als matrices bevatten met behulp van OPENJSON met een expliciet schema. Wanneer ik naar een sleutel in de WITH-component verwijst, worden alleen scalaire waarden geretourneerd. Objecten en matrices worden geretourneerd als null-waarden. Hoe kan ik objecten of arrays extraheren als JSON-objecten?

Answer. Als u een object of matrix als kolom wilt retourneren, gebruikt u de as-JSON-optie in de kolomdefinitie, zoals wordt weergegeven in het volgende voorbeeld.

SELECT scalar1, scalar2, obj1, obj2, arr1  
FROM OPENJSON(@json)  
    WITH ( scalar1 int,  
        scalar2 datetime2,  
        obj1 NVARCHAR(MAX) AS JSON,  
        obj2 NVARCHAR(MAX) AS JSON,  
        arr1 NVARCHAR(MAX) AS JSON)

Retourneer lange tekstwaarde met OPENJSON in plaats van JSON_VALUE

Question. Ik heb een beschrijvingssleutel in JSON-tekst die lange tekst bevat. JSON_VALUE(@json, '$.description') retourneert NULL in plaats van een waarde.

Answer. JSON_VALUE is ontworpen om kleine scalaire waarden te retourneren. Over het algemeen retourneert de functie NULL in plaats van een overloopfout. Als u langere waarden wilt retourneren, gebruikt u OPENJSON, die NVARCHAR(MAX)-waarden ondersteunt, zoals wordt weergegeven in het volgende voorbeeld.

SELECT myText FROM OPENJSON(@json) WITH (myText NVARCHAR(MAX) '$.description')

Dubbele sleutels afhandelen met OPENJSON in plaats van JSON_VALUE

Question. Ik heb dubbele sleutels in de JSON-tekst. JSON_VALUE retourneert alleen de eerste sleutel die op het pad is gevonden. Hoe kan ik alle sleutels met dezelfde naam retourneren?

Answer. De ingebouwde scalaire JSON-functies retourneren alleen het eerste exemplaar van het object waarnaar wordt verwezen. Als u meer dan één sleutel nodig hebt, gebruikt u de functie OPENJSON-tabelwaarde, zoals wordt weergegeven in het volgende voorbeeld.

SELECT value FROM OPENJSON(@json, '$.info.settings')  
WHERE [key] = 'color'

OPENJSON vereist compatibiliteitsniveau 130

Question. Ik probeer uit te voeren OPENJSON in SQL Server 2016 en ik krijg de volgende fout.

Msg 208, Level 16, State 1 'Invalid object name OPENJSON'

Answer. De OPENJSON functie is alleen beschikbaar onder compatibiliteitsniveau 130. Als uw databasecompatibiliteitsniveau lager is dan 130, wordt de OPENJSON verborgen. Andere JSON-functies zijn beschikbaar op alle compatibiliteitsniveaus.

Andere vragen

Verwijzingssleutels die niet-alfanumerieke tekens bevatten in JSON-tekst

Question. Ik heb niet-alfanumerieke tekens in sleutels in mijn JSON-tekst. Hoe kan ik naar deze eigenschappen verwijzen?

Answer. U moet deze tussen aanhalingstekens in JSON-paden plaatsen. Bijvoorbeeld JSON_VALUE(@json, '$."$info"."First Name".value').

Meer informatie over JSON in de SQL Database Engine

Zie de volgende video's voor een visuele inleiding tot de ingebouwde JSON-ondersteuning:

  • Last updated on