3
votes

L'objet SQL Server-JSON contient à la fois des chaînes et des tableaux: comment obtenir un résultat avec une seule requête?

Les données que j'essaie d'analyser contiennent parfois une seule valeur de chaîne, d'autres fois, elles contiennent un tableau de valeurs.

Par exemple, cela fonctionne bien:

SELECT
    color
FROM 
    OPENJSON('{"name": "Christmas Tree","color": "Green"}') 
WITH (color NVARCHAR(MAX) '$.color' AS JSON)

json sql-server

3 commentaires

La solution «triviale» est de UNION ALL les deux requêtes puis de filtrer celle qui n'a pas NULL. Mais ce n'est probablement pas utile pour des exemples réels

Malheureusement, j'essaye d'assigner le résultat à une variable, donc je ne peux pas utiliser UNION ALL directement. Je suppose que je pourrais le vider dans une table temporaire, puis affecter la variable à partir de là.

Vous pouvez l'affecter à une variable. Permet de faire une course et de voir qui travaille en premier;)

3 Réponses :

1
votes

Cela fera ce que vous voulez. Encore une fois, c'est probablement trop compliqué pour les situations du monde réel, mais cela peut vous donner un aperçu

DECLARE @JSON VARCHAR(100)
DECLARE @V VARCHAR(50)

SET @JSON = '{"name": "Candy Cane","color": ["Red","White"]}'


SELECT @V = colour
FROM (
SELECT
    color as colour
FROM 
    OPENJSON(@JSON) 
WITH    (color  NVARCHAR(MAX) '$.color')  
UNION ALL
SELECT
    color as colour
FROM 
    OPENJSON(@JSON) 
WITH    (color  NVARCHAR(MAX) '$.color' AS JSON)  
) ST
WHERE colour IS NOT NULL
ORDER BY colour

SELECT @V

Il est important de noter le ORDER BY . Si cet ensemble de données renvoie jamais deux lignes valides, vous obtiendrez au moins systématiquement la même réponse.

De plus, je n'ai pas pu m'empêcher de renommer en couleur;)

3 commentaires

Cela fonctionnera certainement pour mes besoins, et malheureusement, ce ne sera pas la partie la plus laide de mon code jusqu'à présent :) Si personne ne propose une solution plus élégante, je la marquerai comme une solution.

haha oui JSON au relationnel sera toujours moche car une définition de schéma cohérente est juste un peut être lancé sur la route du gars de la base de données.

Il est possible de décoder la couleur en 2 champs: variantes scalaires et tableau en un seul passage. Après cela, nous pouvons remplacer les UNION par des ISNULL

1
votes

Un peu douteux, mais ça marche:

DECLARE @json NVARCHAR(MAX) = N'{"name": "Christmas Tree","color": "Green"}';
;WITH Data AS (
    SELECT CASE WHEN ISJSON(j.value) > 0 THEN j.[value] ELSE '["'+j.value+'"]' END AS [value] 
    FROM OPENJSON(@json) j 
    WHERE j.[key] = 'color'
)
SELECT d.[value] AS [color] 
FROM Data j 
CROSS APPLY OPENJSON(j.[value]) d
;

0 commentaires

2
votes

Vous pouvez décoder un champ json sous plusieurs formes en une seule passe:

DECLARE @data varchar(max)
SET @data = '[{"name": "Christmas Tree","color": "Green"}, {"name": "Christmas Tree","color": ["Green", "Yellow"]}]'


SELECT
    ISNULL(color_array, color_scalar) as colour
FROM 
    OPENJSON(@data) 
WITH (
color_array nvarchar(max) '$.color' as JSON,
color_scalar nvarchar(max) '$.color')

Est-ce ce que vous voulez?

2 commentaires

Cela fonctionne et est un peu plus propre que la solution UNION ALL. Merci!

Bienvenue dans stackoverflow! Je peux vous féliciter: votre question était étonnamment bonne pour la première tentative!