Verschachteltes JSON mit SQL

[Domeus]

Hallo zusammen.

Ich habe folgendes SQL:

EXEC SQL SELECT
json_object('Confirmation':
json_object(
'Datum': date, 'Header':
json_object(
'company': company,
'delivery_line': json_arrayagg(
json_object(
'package_number': trim(xpackage_no),
'country_of_origins': json_array(
json_object(
'country_of_origin': trim(country_from,
'quantity_picked': trim(quan))))))))

INTO :jsonfile
FROM qtemp/jsonout
group by company;

Bei folgenden Daten:

date	company	xpackage_no	country_from	quan
20230901	123	12345	DE	5
20230901	123	12345	AT	1

bekomme ich folgendes JSON:

{
    "Confirmation": {
        "Datum": "20230901",
        "Header": {
            "company": "123    "
        },
        "delivery_line": [
            {
                "package_number": "12345",
                "country_of_origins": [
                    {
                        "country_of_origin": "DE",
                        "quantity_picked": "5"
                    }
                ]
            }
        ]
        "delivery_line": [
            {
                "package_number": "12345",
                "country_of_origins": [
                    {
                        "country_of_origin": "AT",
                        "quantity_picked": "1"
                    }
                ]
            }
        ]
    }
}

ich möchte das JSON aber folgendermaßen formatiert haben:

{
    "Confirmation": {
        "Datum": "20230901",
        "Header": {
            "company": "123    "
        },
        "delivery_line": [
            {
                "package_number": "12345",
                "country_of_origins": [
                    {
                        "country_of_origin": "DE",
                        "quantity_picked": "5"
                    }
                    {
                        "country_of_origin": "AT",
                        "quantity_picked": "1"
                    }
                ]
            }
        ]
    }
}

Was muss ich dazu ändern? Irgendwie klappt das bei mir nicht.

Schon mal vielen Dank und Gruß
Sebastian

[Fuerchau]

Bei "'Name' value" kann ein "(select...)" oder "JSON_OBJECT" wieder folgen.

https://www.ibm.com/docs/en/i/7.2?to...enerating-json

[xenofob]

Hi,

es gibt ein Problem bei delivery_line: nach Company muss noch eine Klammer zu hinzugefügt werden..
Und zum Array:
Da kann man sich mit einem weiteren select + Json_arrayagg weiterhelfen. Sollte in etwa so aussehen:

json_object('Confirmation':
json_object(
'Datum': date, 'Header':
json_object(
'company': company),
'delivery_line': json_arrayagg(
json_object(
'package_number': trim(xpackage_no),
'country_of_origins': 
(select json_arrayagg(
  json_object(
    'country_of_origin': trim(country_from),
    'quantity_picked': trim(quan)
   )
 )
from qtemp/jsonout b 
where a.date = b.date and 
           a.company = b.company and
          a.xpackage_no = b.xpackage_no
) format json
))))

INTO :jsonfile
FROM qtemp/jsonout a
group by date, company;

[Domeus]

Vielen Dank für die Antworten!

@xenofob

ich habe das so umgesetzt und bekomme nun aber folgendes raus (nicht wundern das die Werte nicht gleich sind. Im Beispiel oben hatte ich ausgedachte Felder und Werte und nun die aus einem Testauftrag):

"country_of_origins": "[{\"country_of_origin\":\"IT\",\"quantity_picked\":\"200\"},{\"country_of_origin\":\"SI\",\"quantity_picked\":\"10\"}]"

Der Inhalt sieht soweit erstmal gut aus aber es scheint, dass er das ganze nun als ein VALUE behandelt und nicht als JSON Format. Zumindest ist der ganze Teil in ""

Wo mache ich noch was falsch?

Gruß
Sebastian

[Andreas_Prouza]

Dafür ist die Syntax "format json" zuständig, das es als JSON hineingebettet wird.

[Domeus]

Erstmal vielen dank! Das "format json" hat geholfen.


Jetzt habe ich nur noch ein Problem. Meine Ausgabe sieht jetzt folgendermassen aus:

{
    "Confirmation": {
        "Datum": "20230901",
        "Header": {
            "company": "123    "
        },
        "delivery_line": [
            {
                "package_number": "12345",
                "country_of_origins": [
                    {
                        "country_of_origin": "DE",
                        "quantity_picked": "5"
                    }
                    {
                        "country_of_origin": "AT",
                        "quantity_picked": "1"
                    }
                ]
            }
        {
                "package_number": "12345",
                "country_of_origins": [
                    {
                        "country_of_origin": "DE",
                        "quantity_picked": "5"
                    }
                    {
                        "country_of_origin": "AT",
                        "quantity_picked": "1"
                    }
                ]
            }
        ]
    }
}

Heisst ich habe nun 2 mal diesen Block weil ich ja in der Datendatei 2 Sätze habe. Hab schon verschiedenes versucht aber er fasst mir diese nicht zusammen.

EDIT: Habe jetzt selbst eine Lösung gefunden, indem ich jeweils noch einen zusätzlichen Eintrag mit country_of_origin = *BLANK und quantity_picked = *BLANK hinzufüge und setze dann dem FROM qtemp/jsonout a ein WHERE country_of_origin = *BLANK und bei dem eingebettenten SELECT ein where country_of_orign <> *BLANK. Trotzdem würde mich noch eine saubere SQL Lösung interessieren.

Gruß
Sebastian

[xenofob]

Das sollte nicht so aussehen.
Kannst du den aktuellen sql posten?