JSON Daten mit SQL auslesen

[Kerki]

Hallo liebe Community,

ich will folgende JSON mit SQL auslesen.


{ "indexFields": [
{
"fieldName": "ID",
"fieldLabel": "ID",
"item": 119
},
{
"fieldName": "USER",
"fieldLabel": "Abgelegt von",
"item": "ADMIN"
},
{
"fieldName": "DATE",
"fieldLabel": "Datum",
"item": "2020-11-17T00:00:00"
},
{
"fieldName": "POSITIONEN",
"fieldLabel": "Positionen",
"item": {
"Row": [
{
"ColumnValue": [
{
"PointAndShootInfo": null,
"FieldName": "POSIT_ARTIKELNUMMER",
"FieldLabel": "Artikelnummer",
"IsNull": false,
"ReadOnly": false,
"Item": "12345",
"ItemElementName": 6
},
{
"PointAndShootInfo": null,
"FieldName": "POSIT_ARTIKELBEZEICHNUNG",
"FieldLabel": "Artikelbezeichnung",
"IsNull": false,
"ReadOnly": false,
"Item": "Produkt Fridolin",
"ItemElementName": 6
},
{
"PointAndShootInfo": null,
"FieldName": "POSIT_EAN1",
"FieldLabel": "EAN",
"IsNull": false,
"ReadOnly": false,
"Item": 1234567891246,
"ItemElementName": 2
},
{
"PointAndShootInfo": null,
"FieldName": "POSIT_MENGE",
"FieldLabel": "Menge",
"IsNull": false,
"ReadOnly": false,
"Item": 5,
"ItemElementName": 2
}
]
},
{
"ColumnValue": [
{
"PointAndShootInfo": null,
"FieldName": "POSIT_ARTIKELNUMMER",
"FieldLabel": "Artikelnummer",
"IsNull": false,
"ReadOnly": false,
"Item": "65478",
"ItemElementName": 6
},
{
"PointAndShootInfo": null,
"FieldName": "POSIT_ARTIKELBEZEICHNUNG",
"FieldLabel": "Artikelbezeichnung",
"IsNull": false,
"ReadOnly": false,
"Item": "Produkt Hugo",
"ItemElementName": 6
},
{
"PointAndShootInfo": null,
"FieldName": "POSIT_EAN1",
"FieldLabel": "EAN",
"IsNull": false,
"ReadOnly": false,
"Item": 1234567991249,
"ItemElementName": 2
},
{
"PointAndShootInfo": null,
"FieldName": "POSIT_MENGE",
"FieldLabel": "Menge",
"IsNull": false,
"ReadOnly": false,
"Item": 14,
"ItemElementName": 2
}
]
}
]
}
},
{
"fieldName": "DISK",
"fieldLabel": "Disk Number",
"item": 1
},
{
"fieldName": "DISKNO",
"fieldLabel": "Disk Number",
"item": 1
}
]
}

Wie greife ich auf die Liste von "Row.ColumnValue" zu?

Ich kann mit folgendem Select auf die Liste "indexFields" zugreifen aber nicht die darin liegenden.


select * from JSON_TABLE(:json,'$.indexFields
'
COLUMNS (fieldName VARCHAR(100) PATH '$.fieldName',
fieldLabel VARCHAR(100) PATH '$.fieldLabel',
item VARCHAR(100) PATH '$.item'
)) AS X;

Ich danke euch im voraus und liebe Grüße
Kerki

[Andreas_Prouza]

Hallo,

beim JSON_TABLE greifst du mit dem XPATH auf der Ebene zu, die du definiert hast.
Mit "$.indexFields" ist dein XPATH eben ganz oben beim ersten Element.
Möchtest du auf Subelemente in deinem Baum zugreifen gibt es 2 Möglichkeiten:
* bei der Spalten definition
.. PointAndShootInfo varchar(100) PATH '$.item.Row[0].ColumnValue[0].PointAndShootInfo'
* oder du greifst separat mit SQL auf diese Ebene zu. Dafür musst du den XPATH entsprechend gesetzt haben.

Da ROW und ColumnValue mehrere Subfelder beinhalten können musst du hier die "Zeilennummer" mitangeben.

lg Andreas

[B.Hauser]

An dieser Stelle musst Du mit verschachtelten (NESTED) Anweisungen arbeiten.
The powerful JSON_TABLE function (https://developer.ibm.com/technologies/systems/articles/i-json-table-trs/)

Etwa so:

Select x.*
from JSON_TABLE(YourJSONDocument,
'$.Staff.Department '
Columns("DeptName" VarChar(50) ,
Nested '$.Employee '
Columns( FirstName VarChar(50) Path 'lax $.Name.FirstName',
LastName VarChar(50) Path 'lax $.Name.LastName',
....


Birgitta

[Kerki]

Danke für die schnelle Antwort aber ich komm immer nur auf SQL Fehlercode 22002.


select * from JSON_TABLE(:json,'$.indexFields.item
'
COLUMNS (fieldName VARCHAR(100) PATH '$.fieldName',
fieldLabel VARCHAR(100) PATH '$.fieldLabel',
item VARCHAR(100) PATH '$.item',


NESTED '$.item.Row
.ColumnValue
'
COLUMNS (FieldName1 VARCHAR(100) PATH 'lax $FieldName',
FieldLabel1 VARCHAR(100) PATH 'lax $FieldLabel',
Item1 VARCHAR(100) PATH 'lax $Item'
))) AS X;

Oder muss ich jetzt nochmal in der Row
ein Nested machen und für die ColumnValue eine Collumns anlegen?

Gruß Kerki

[Robi]

Birgitta hat: from JSON_TABLE(YourJSONDocument),
Du hast: select * from JSON_TABLE(:json, da fehlt eine )

[Fuerchau]

Der Fehler-Code 22002 (SQLCODE -305) deutet beim Fetch auf einen fehlenden NULL-Anzeiger hin.
Bei den Into-Variablen des Fetch benötigst du int(5)-Felder für die Prüfung auf NULL-Inhalte.

[B.Hauser]

Du musst aufpassen, wo Du aufsetzst.
Das Array ist indexFields und nicht item .

Versuch's mal so:

select * from JSON_TABLE(:JSON,'$.indexFields '
COLUMNS (fieldName VARCHAR(100) PATH '$.fieldName',
fieldLabel VARCHAR(100) PATH '$.fieldLabel',
item VARCHAR(100) PATH '$.item',
NESTED '$.item.Row .ColumnValue '
COLUMNS (FieldName1 VARCHAR(100) PATH '$.FieldName' Default '' on empty,
FieldLabel1 VARCHAR(100) PATH '$.FieldLabel',
Item1 VARCHAR(100) PATH '$.Item'
))) AS X;

NULL-Werte kann man entweder einer Default-Anweisung bei der Spalten-Definition im JSON_TABLE oder der Funktion COALESCE in der SELECT-Zeile in Default-Werte konvertieren.

Birgitta

[Thomas65]

Super, wieder was gelernt! Dieses Forum ist klasse, Dank hier an Birgitta.

Bisher habe ich nested arrays immer komplett in entsprechend lange CHAR-Felder ausgelesen und diese dann später in anderen Procedures wiederum mit JSON_TABLE verarbeitet.

Gerade wenn man mehr als ein eingebettetes Array im JSON hat kann das für die Struktur des Programms durchaus nicht schlecht sein. Immer vorausgesetzt, dass die Datenmengen je Array überschaubar sind.

Thomas

[Kerki]

Ich danke euch vielmals für die tolle Hilfe.

In diesem Forum findet man immer kompetente Hilfe.

Hier nochmal die funktionierende SQL Anweisung:


select * from JSON_TABLE(:JSON,'$.indexFields
' COLUMNS (fieldName VARCHAR(100) PATH '$.fieldName' Default '' on empty,
fieldLabel VARCHAR(100) PATH '$.fieldLabel' Default '' on empty,
item VARCHAR(100) PATH '$.item' Default '' on empty,


NESTED '$.item.Row
.ColumnValue
'
COLUMNS (FieldName1 VARCHAR(100) PATH '$.FieldName' Default '' on empty,
FieldLabel1 VARCHAR(100) PATH '$.FieldLabel' Default '' on empty,
Item1 VARCHAR(100) PATH '$.Item' Default '' on empty
))) AS X;