[NEWSboard IBMi Forum]
  1. #1
    Registriert seit
    Aug 2018
    Beiträge
    7

    JSON Daten mit SQL auslesen

    Hallo liebe Community,

    ich will folgende JSON mit SQL auslesen.

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

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

  2. #2
    Registriert seit
    Nov 2020
    Beiträge
    315
    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

  3. #3
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    An dieser Stelle musst Du mit verschachtelten (NESTED) Anweisungen arbeiten.
    The powerful JSON_TABLE function

    Etwa so:
    Code:
    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
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  4. #4
    Registriert seit
    Aug 2018
    Beiträge
    7
    Danke für die schnelle Antwort aber ich komm immer nur auf SQL Fehlercode 22002.

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

  5. #5
    Registriert seit
    Jun 2001
    Beiträge
    1.973
    Birgitta hat: from JSON_TABLE(YourJSONDocument),
    Du hast: select * from JSON_TABLE(:json, da fehlt eine )
    Das Notwendige steht über dem technisch machbaren.
    (klingt komisch, funktioniert aber!)

  6. #6
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    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.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  7. #7
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Du musst aufpassen, wo Du aufsetzst.
    Das Array ist indexFields und nicht item .

    Versuch's mal so:
    Code:
    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
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  8. #8
    Registriert seit
    Oct 2013
    Beiträge
    9
    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

  9. #9
    Registriert seit
    Aug 2018
    Beiträge
    7
    Ich danke euch vielmals für die tolle Hilfe.

    In diesem Forum findet man immer kompetente Hilfe.

    Hier nochmal die funktionierende SQL Anweisung:

    Code:
    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;

Similar Threads

  1. Rest Anfrage und Json return
    By Robi in forum NEWSboard Programmierung
    Antworten: 12
    Letzter Beitrag: 08-04-20, 16:01
  2. JSON Daten mit JSON_TABLE
    By Peet in forum NEWSboard Programmierung
    Antworten: 4
    Letzter Beitrag: 18-03-20, 13:36
  3. HTTPS Aufruf mit JSON Input
    By derMuller in forum IBM i Hauptforum
    Antworten: 4
    Letzter Beitrag: 05-12-17, 12:05
  4. JSON in RPG verarbeiten
    By dschroeder in forum NEWSboard Programmierung
    Antworten: 13
    Letzter Beitrag: 13-07-16, 15:23

Tags for this Thread

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •