JSON Daten mit JSON_TABLE

[Peet]

Hallo zusammen,

ich bekomme folgende JSON-Daten von einem Lieferanten:



{ "numeric": { "value.common.machine.hours.operation.total": { "time": 1584353295000, "value": 1019.1 }}}


Die wollte ich wie folgt mit SQL verarbeiten:


select t.* FROM testlib.JSTEST_MES, JSON_TABLE(JSTEST_MES.jsondoc,'lax $' COLUMNS ( bh_zeit VARCHAR(20) PATH 'lax $.value.common.machine.hours.operation.total.time' , bh_gesamt dec(13, 2) PATH 'lax $.value.common.machine.hours.operation.total.value ' ) ) as t


Ich habe auch schon "numeric.value.common.machine.hours.operation.total .time" bzw. "value" ausprobiert, leider bekomme ich nur 2 leere Felder !



BH_ZEIT | BH_GESAMT |
----------------------
| |
----------------------


Ich habe es auch schon mit "nested" probiert, leider alles ohne Erfolg :=(

Das ganze mache ich in einem Net.Data Makro.

Andere JSON-Daten vom gleichen Lieferanten kann ich problemlos einlesen.
Die haben z.B. folgende Struktur...


{
"id": "123456",
"name": "Maschine1",
"serial": "1234567890",
"pin": "XYZ0123VOB0002119",
"vin": null,
"organization": {
"id": "c5e82e09-4b7a-41e9-8fda-88bbe16d30ab",
"name": "Firma Meier [OU]"
},
"communicationUnits": [
{
"id": "987520072577766",
"name": "987520072577766",
"state": "live"
}
],
"model": {
"id": "558976b0-3efc-4736-a6f7-2a0181f19b96",
"name": "V50 Diesel"
}
}

Ich habe ja das "numeric" in Verdacht, denn alle anderen funktionerenden JSON-Daten haben eine "eindeutige" Struktur. (ich meine damit die beginnen mit einem "Feld", ich weiß nicht wie ich das anders ausdrücken soll)

Kann mir jemand helfen ???
Vielen Dank im Voraus !

[B.Hauser]

Das Problem ist nicht "numeric", sondern die Punkte in "value.common.machine.hours.operation.total".
Punkte in einem Pfad anzugeben, bedeutet eine Verschachtelung.
Damit wird der Wert nicht mehr gefunden. Wenn Du allerdings die Pfad-Angabe "value.common..." in der richtigen Groß/Kleinschreibung in Anführungszeichen einbettest sollte es funktionieren.

Bei mir funktioniert zumindest das folgende Statement:

Select *
from JSON_TABLE('{ "numeric": { "value.common.machine.hours.operation.total":
{ "time": 1584353295000, "value": 1019.1 }}}',
'$.numeric."value.common.machine.hours.operation.total"'
Columns(Zeit Varchar(20) path '$.time',
Wert Decimal(11, 2) path '$.value')) x;

Birgitta

[Peet]

Hallo Birgitta,
vielen Dank und WOW!

Während der vielen erfolglosen Stunden des Testens kam mir zwischendurch auch mal der Gedanke, dass der "Punkt" im SQL-Befehl in der Regel nicht ohne Maskierung angegeben werden kann, habe mich dann aber wegen dem Begriff "path" in der Doku fehlleiten lassen. (..kann man nachher immer sagen, ich weiß, ist aber so :=))

Meine JSON-Daten sind nun aber komplexer als das Beispiel, das hatte ich nur verkürzt um hier nach Unterstützung zu fragen.

Ich habe also noch andere "Pfade/Daten", die haben alle dann die Felder "time" und "value".
Jetzt habe ich das versucht entsprechend deiner Lösung einzubauen, bin aber wieder nicht erfolgreich.
Ich habe das "numeric" sowohl bei "json_table" wie auch in den COLUMNS versucht, leider wieder ohne Erfolg. :=(

Hier mein Code, hier noch nur für die 2 Felder aus deiner Lösung...
/(bh_time ist hier 13,0 weil ich aus der Unixzeit einen Zeitstempel mache)

...FROM testlib.JSTEST_MES, JSON_TABLE(JSTEST_MES.jsondoc,'lax $' COLUMNS ( bh_zeit dec(13, 0) PATH 'lax $.numeric."value.common.machine.hours.operation.total.time"', bh_gesamt dec(11, 2) PATH 'lax $.numeric.".value.common.machine.hours.operation.total.value"') ) as t

Was mache ich da noch falsch ???

Danke vorab für die Mühe :=)

[B.Hauser]

Also bei mir klappt auch das folgende Statement:

Select *
from JSON_TABLE('{ "numeric": { "value.common.machine.hours.operation.total":
{ "time": 1584353295000, "value": 1019.1 }}}',
'lax $'
Columns(BH_Zeit Decimal(13, 0) path 'lax $.numeric."value.common.machine.hours.operation.total".time',
BH_Gesamt Decimal(11, 2) path 'lax $.numeric."value.common.machine.hours.operation.total".value')) x;

Was mir bei Deinem Beispiel auffällt, du hast zumindest bei BH_Gesamt einen Punkt nach dem ersten Anfürhrungszeichen zuviel.

Birgitta

[Peet]

Hallo Birgitta,

Teufelskram und Hexenwerk :=)
Danke !!
Der Fehler war natürlich zum einen der von dir erkannte, zuviel gesetzte "Punkt" bei BH_GESAMT.
Falsch war auch noch ...

$.numeric."value.common.machine.hours.operation.total.time"',
...die Anführungszeichen nach ".time" gehören davor !!!

So sieht der entsprechende Teil des SQL-Befehl nun aus...:

JSON_TABLE(JSTEST_MES.jsondoc,'lax $' COLUMNS ( bh_zeit dec(13, 0) PATH 'lax $.numeric."value.common.machine.hours.operation.total".time', bh_gesamt dec(11, 2) PATH 'lax $.numeric."value.common.machine.hours.operation.total".value', bh_service_zeit dec(13, 0) PATH 'lax $.numeric."value.common.machine.service.hourstonextservice".time', bh_service_gesamt dec(11, 2) PATH 'lax $.numeric."value.common.machine.service.hourstonextservice".value', maschine_geo_latitude_zeit dec(13, 0) PATH 'lax $.numeric."value.common.machine.geo.latitude".time', maschine_geo_latitude_daten dec(11, 2) PATH 'lax $.numeric."value.common.machine.geo.latitude".value', maschine_geo_longitude_zeit dec(13, 0) PATH 'lax $.numeric."value.common.machine.geo.longitude".time', maschine_geo_longitude_daten dec(11, 2) PATH 'lax $.numeric."value.common.machine.geo.longitude".value') ) as t
Und er funktioniert :=)
TAUSEND Dank, Birgitta !!!
Vg.

...und bleibt alle aufmerksam und Gesund !!!