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:
Code:
{
    "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:
Code:
{
    "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