wake-up-neo.com

SQL to JSON - Array von Objekten zu Array von Werten in SQL 2016

SQL 2016 verfügt über eine neue Funktion, mit der Daten auf SQL Server in JSON konvertiert werden. Ich habe Schwierigkeiten, ein Array von Objekten zu einem Array von Werten zu kombinieren, d.h.

BEISPIEL -

CREATE TABLE #temp (item_id VARCHAR(256))

INSERT INTO #temp VALUES ('1234'),('5678'),('7890')

SELECT * FROM #temp

--convert to JSON

SELECT (SELECT item_id 
FROM #temp
FOR JSON PATH,root('ids'))

ERGEBNIS -

{
    "ids": [{
        "item_id": "1234"
    },
    {
        "item_id": "5678"
    },
    {
        "item_id": "7890"
    }]
}

Aber ich möchte das Ergebnis als -

"ids": [
        "1234",
        "5678",
        "7890"
    ]

Kann mir bitte jemand helfen?

sqljsonsql-server-2016
27
Meghana Raj Jayanarasimha

Vielen Dank! Die Lösung, die wir gefunden haben, konvertiert zuerst in XML -

SELECT  
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + item_id + '"' 
FROM #temp FOR XML PATH('')),1,1,'') + ']' ) ids  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER
17
Meghana Raj Jayanarasimha

Martin!

Ich glaube, das ist noch einfacher:

    SELECT '"ids": ' + 
    REPLACE( 
      REPLACE( (SELECT item_id FROM #temp FOR JSON AUTO),'{"item_id":','' ),
      '"}','"' )
12
Magne Rekdal
declare @temp table (item_id VARCHAR(256))

INSERT INTO @temp VALUES ('123"4'),('5678'),('7890')

SELECT * FROM @temp

--convert to JSON

select 
    json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(item_id, 'json') + '"', char(44)))) as [json]
from @temp
for json path

Wenn wir Strings als JSON-Array verketten wollen, dann:

1) Escape-Zeichenfolge - STRING_ESCAPE

2) String mit Komma-Trennzeichen verketten - STRING_AGG, Komma-ASCII-Code ist 44

3) Anführungszeichen in Klammern setzen - QUOTENAME (ohne Parameter)

4) gebe einen String (mit einem Array von Elementen) als json - JSON_QUERY zurück

6
massther

Da Arrays mit primitiven Werten gültiges JSON sind, scheint es seltsam, dass eine Funktion zum Auswählen von Arrays mit primitiven Werten nicht in die JSON-Funktionalität von SQL Server integriert ist. (Wenn im Gegenteil eine solche Funktionalität vorhanden ist, konnte ich sie nach längerer Suche zumindest nicht entdecken.).

Der oben beschriebene Ansatz funktioniert wie beschrieben. Wenn Sie jedoch ein Feld in einer größeren Abfrage anwenden, wird das Array der Grundelemente in Anführungszeichen gesetzt.

Zum Beispiel dieses

DECLARE @BomTable TABLE (ChildNumber dbo.udt_ConMetPartNumber);
INSERT INTO @BomTable (ChildNumber) VALUES (N'101026'), (N'101027');
SELECT N'"Children": ' + REPLACE(REPLACE((SELECT ChildNumber FROM @BomTable FOR JSON PATH), N'{"ChildNumber":', N''), '"}','');

arbeitet durch das Produzieren:

"Children": ["101026,"101027]

Aber wie oben beschrieben:

SELECT
    p.PartNumber,
    p.Description,
    REPLACE(REPLACE((SELECT
                        ChildNumber
                     FROM
                        Part.BillOfMaterials
                     WHERE
                        ParentNumber = p.PartNumber
                     ORDER BY
                        ChildNumber
                    FOR
                     JSON AUTO
                    ), N'{"ChildNumber":', N''), '"}', '"') AS [Children]
FROM
    Part.Parts AS p
WHERE
    p.PartNumber = N'104444'
FOR
    JSON PATH

Produziert:

[
    {
        "PartNumber": "104444",
        "Description": "ASSY HUB           R-SER  DRIV HP10  ABS",
        "Children": "[\"101026\",\"101027\",\"102291\",\"103430\",\"103705\",\"104103\"]"
    }
]

Wobei das Children -Array als Zeichenfolge umbrochen wird.

4
CalvinDale

Die meisten dieser Lösungen erstellen im Wesentlichen eine CSV, die den Array-Inhalt darstellt, und setzen diese CSV dann in das endgültige JSON-Format. Folgendes verwende ich, um XML zu vermeiden:

DECLARE @tmp NVARCHAR(MAX) = ''

SELECT @tmp = @tmp + '"' + [item_id] + '",'
FROM #temp -- Defined and populated in the original question

SELECT [ids] = JSON_QUERY((
    SELECT CASE
        WHEN @tmp IS NULL THEN '[]'
        ELSE '[' + SUBSTRING(@tmp, 0, LEN(@tmp)) + ']'
        END
    ))
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
1
Eric