JSON, doppelte Werte vermeiden

[Ranger]

Hallo,

ich habe nachfolgende (vereinfachte) Tabellen-Struktur

JSON_GRP1	JSON_GRP2	JSON_GRP3	JSON_GRP4
ID	ID	ID	ID
Name	Mail	Data1	Data2
1:	1:	n:	n

und ich versuche diese Datenstruktur mittels SQL in ein JSON zu pressen.

SELECT 
CHAR(JSON_OBJECT(
 'Info' VALUE JSON_OBJECT(
    'Id' VALUE JSON_GRP1.ID
    , 'Name' VALUE JSON_GRP1.NAME
            )
            
, 'Group2' VALUE JSON_OBJECT(
    'Email' VALUE TRIM(JSON_GRP2.MAIL)
            )
            
, 'Data' VALUE JSON_OBJECT(
                'Data1' VALUE json_arrayagg ( json_object ('Id' value trim(JSON_GRP3.DATA1) null on null))
                , 'Data2' VALUE json_arrayagg ( json_object ('Id' value trim(JSON_GRP4.DATA2) null on null))
            )
        
), 1000)
FROM JSON_GRP1
  JOIN JSON_GRP2 ON JSON_GRP1.ID = JSON_GRP2.ID
  LEFT JOIN JSON_GRP3 ON JSON_GRP1.ID = JSON_GRP3.ID
  LEFT JOIN JSON_GRP4 ON JSON_GRP1.ID = JSON_GRP4.ID
             
GROUP BY  JSON_GRP1.ID, JSON_GRP1.NAME, JSON_GRP2.MAIL

im Ergebnis kommt folgendes JSON heraus:

{"Info":{"Id":1,"Name":"Tester"}
,"Group2":{"Email":"tester@test.de"}
,"Data":{
"Data1":[{"Id":"11"},{"Id":"11"},{"Id":"12"},{"Id":"12"}]
,"Data2":[{"Id":"21"},{"Id":"22"},{"Id":"21"},{"Id":"22"}]}}

Wie bekomme ich die (markierten) doppelten Elemente weg, damit ich folgendes Ergebnis erhalte:

{"Info":{"Id":1,"Name":"Tester"}
,"Group2":{"Email":"tester@test.de"}
,"Data":{
"Data1":[{"Id":"11"},{"Id":"12"}]
,"Data2":[{"Id":"21"},{"Id":"22"}]}}

CREATE TABLE JSON_GRP1("ID" NUMERIC (5 ) NOT NULL WITH DEFAULT, NAME VARCHAR(20 ) NOT NULL WITH DEFAULT);
CREATE TABLE JSON_GRP2("ID" NUMERIC (5 ) NOT NULL WITH DEFAULT, MAIL VARCHAR(60 ) NOT NULL WITH DEFAULT);
CREATE TABLE JSON_GRP3("ID" NUMERIC (5 ) NOT NULL WITH DEFAULT, DATA1 NUMERIC (5 ) NOT NULL WITH DEFAULT);
CREATE TABLE JSON_GRP4("ID" NUMERIC (5 ) NOT NULL WITH DEFAULT, DATA2 NUMERIC (5 ) NOT NULL WITH DEFAULT);

INSERT INTO JSON_GRP1 VALUES (1, 'Tester');
INSERT INTO JSON_GRP2 VALUES (1, 'tester@test.de');
INSERT INTO JSON_GRP3 VALUES (1, 11);
INSERT INTO JSON_GRP3 VALUES (1, 12);
INSERT INTO JSON_GRP4 VALUES (1, 21);
INSERT INTO JSON_GRP4 VALUES (1, 22);

[Fuerchau]

Dein doppelter Left-Join multipliziert das Ergebnis.
Du musst dir das so vorstellen:

select * from (

select * from Table
left join table2 .,..
) t2

left join table3 ...

Jeder left join führt zu einem Zwischenergebnis, dass mit dme nächsten Join verknüpft wird. Du benötigst also mindestens eine 1:1 statt 1:N Beziehung.

[Andreas_Prouza]

Hallo,

Wenn du das alles in 1 Select haben willst ist das SQL etwas komplexer.
Du musst die Data-Elemente als Subselect ausführen.
Da das ganze auch nicht so einfach ist musst du mit JSON_TO_BSON und umgekehrt die JSON Blöcke zusammen mergen, da SQL diese sonst Escapen und als ganzen Text Block betrachten würde.

Lange Rede, kurzer Sinn, hier wäre das SQL:

SELECT CHAR(JSON_OBJECT(
 'Info' : JSON_OBJECT(
    'Id' : JSON_GRP1.ID
    , 'Name' : JSON_GRP1.NAME
            )            
, 'Group2' : JSON_OBJECT(
    'Email' : JSON_GRP2.MAIL
    )            
, 'Data' : 
	json_array( 
		bson_to_json((SELECT  json_to_bson (JSON_OBJECT('Data1' : json_arrayagg ( json_object ( 'Id' : JSON_GRP3.DATA1 null on null))))
                		FROM JSON_GRP3 where JSON_GRP1.ID = JSON_GRP3.ID))
       , bson_to_json((SELECT json_to_bson (JSON_OBJECT('Data2' : json_arrayagg ( json_object ( 'Id' : JSON_GRP4.DATA2 null on null))))
       					FROM JSON_GRP4 where JSON_GRP1.ID = JSON_GRP4.ID)))
), 1000)
FROM JSON_GRP1
  JOIN JSON_GRP2 ON JSON_GRP1.ID = JSON_GRP2.ID
  LEFT JOIN JSON_GRP4 ON JSON_GRP1.ID = JSON_GRP4.ID            
GROUP BY  JSON_GRP1.ID, JSON_GRP1.NAME, JSON_GRP2.MAIL

lg Andreas

[B.Hauser]

Am einfachsten ist, Du generierst die Daten-Objekte für Grp3 und Grp4 in Common Table Expressions und bindest dann die fertigen Objekte in das JSON-Dokument ein.
Etwa so:

With CTEData1 as (Select Id, 
                         JSON_ArrayAgg( JSON_Object('Id': Trim(Data1))) Data1
                     from json_grp3
                     Group By Id),
     CTEData2 as (Select Id, 
                         JSON_ArrayAgg( JSON_Object('Id': Trim(Data2))) Data2
                     from json_grp4
                     group by Id)
Select Json_Object('info':  JSON_Object('id': a.Id, 'Name': Name),
                   'Goup2': JSON_Object('Email': Mail),
                   'Data':  JSON_Object('Data1': Data1 Format JSON,
                                        'Data2': Data2 Format JSON))
  from json_grp1 a join json_grp2 b on a.id = b.id
                   Left Join CTEData1 c on a.id = c.id
                   Left Join CTEData2 d on a.id = d.id;

Birgitta

[Fuerchau]

Schon mal ausprobiert? Der doppelte Left-Join macht da keinen Unterschied zu vorher. Der CTE hat da keinen Vorteil.
Solange du das 2x 1:N-Problem nicht weg bekommst hast du eine Vervielfältigung.

[B.Hauser]

Hast Du das schon mal ausprobiert?
Vermutlich nicht!
Meine Lösung bringt genau das zurück was angefordert war.
Weil die Daten bereits in den CTEs auf die ID verdichtet werden, gibt es keine Duplikate.

Birgitta

[Ranger]

Schon mal ausprobiert?

Gerade eben, und es scheint, dass beide Varianten funktionieren.
Vielen Dank an Brigitta und an Andreas!