MySQL 5.5 - JSON auslesen

[JoeSixpack]

Hallo!

Ich habe hier eine CMS Datenbank (Joomla) in der ich diverse Daten auslesen möchte. Ein Feld (elements) in einer Tabelle ist dabei offenbar JSON, und da kenne ich mich gar nicht aus.
Nun möchte ich mit einem einfachen SELECT in phpMyAdmin die Daten auslesen und jedes Object in einer Spalte darstellen.

Der Inhalt sieht etwa so aus:

{
"STRASSE": {
"0": {
"value": "Musterstrasse 15"
}
},
"ADRESSE": {
"0": {
"value": "Adresszusatz"
}
},
"PLZORT": {
"0": {
"value": "85542 Test"
}
},
"ADRESSE2": {
"0": {
"value": "Komplette Adresse"
}
},
"TELEFON": {
"0": {
"value": "0568 55412 555"
}
},
"EMAIL": {
"0": {
"value": ""
}
}
}



Vielleicht hat mir hier jemand ein passendes QUERY, ich hab mich schon halb tot gesucht im Netz.

Herzlichen Dank!

 

[Yaslaw]

JSON <> SQL.
Json würde ich über PHP auslesen. Also mit SQL den ganzen JSON-String aus der DB ziehen und mit PHP mittels json_decode() in die Spalten parsen

 

[JoeSixpack]

Hallo Yaslaw
vielen Dank. Da ich das eher selten brauche (und nur "intern"), habe ich ein kleines Tool in Joomla installiert bei dem ich nur noch den Query eingeben kann. Auf "Knopfdruck" kann ich mir dann so ein Excel ausgeben lassen. Aber eben: bei der JSON Abfrage happert es. Und PHP, na ja ist jetzt auch nicht so mein Steckenpferd... Bin mehr der Hobby-Entwickler.

 

[Yaslaw]

Gegenfrage. Ist der Aufbau immer genau gleich?
Also immer nur ein Value pro Eintrag?

Wenn ja, kannst du das in etwa so lösen
Annahme: Die Tabelle heisst tbl und das Json-Feld heisst json.

select
    SUBSTRING_INDEX(SUBSTRING_INDEX(jstrasse, '"', -2), '"', 1) AS strasse,
    SUBSTRING_INDEX(SUBSTRING_INDEX(jadresse, '"', -2), '"', 1) AS adresse,
    SUBSTRING_INDEX(SUBSTRING_INDEX(jplzort, '"', -2), '"', 1) AS plzort,
    SUBSTRING_INDEX(SUBSTRING_INDEX(jemail, '"', -2), '"', 1) AS email
from
    (
        select
            SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"STRASSE":', -1), '},', 1) AS jstrasse,           
            SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"ADRESSE":', -1), '},', 1) AS jadresse,
            SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"PLZORT":', -1), '},', 1) AS jplzort,
            SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"EMAIL":', -1), '},', 1) AS jemail
        from tbl
    ) j;

Erklärung
SUBSTRING_INDEX(json, '"STRASSE":', -1) findet den text "STRASSSE" und gibt alles nach dem ersten Fund zurück:
TXT = {"0": {"value": "Musterstrasse 15"}},"ADRESSE": { ....

SUBSTRING_INDEX(TXT, '},', 1) fndet alles for dem ersten Vorkommen von '},'. Die } ist extra mit drin, um nicht nach einem Komma in einer Adresse abzubrechen
TXT = {"0": {"value": "Musterstrasse 15"}

SUBSTRING_INDEX(jstrasse, '"', -2) nimmt alles nach dem 2ten " von hinten gezählt
TXT = Musterstrasse 15"}

SUBSTRING_INDEX(TXT, '"', 1) nimmt alles vor dem ersten Vorkommen eines "
TXT = Musterstrasse 15

 

[JoeSixpack]

Wow, super! Vielen Dank, das hat mich schon ein gutes Stück weitergebracht. So langsam begreife ich auch die Syntax dahinter....

Gegenfrage. Ist der Aufbau immer genau gleich?
Also immer nur ein Value pro Eintrag?

Ja, der Aufbau bleibt immer gleich, manchmal hat es mehrere Einträge, aber mit dem 3. Substring kann ich das ja rückwärts "abzählen"

Könntest du mir freundlicherweise noch einen Tipp geben, wie ich das noch mit einer "normalen" SQL-Abfrage kombinieren kann? Die wäre:
(die json Abfrage findet in Tabelle "tbl" statt

SELECT i.id,i.name as Name, c.name as Kategorie, c.alias as Alias
FROM `tbl` AS i
JOIN `tbl_cat01` AS ci ON i.id = ci.item_id
JOIN `tbl_cat02` AS c ON ci.category_id = c.id
WHERE c.alias = 'meine-kategorie'

Herzlichen Dank!

 

[Yaslaw]

Nimm in meiner Abfrage noch die ID mit. Dann kannst du meine Abfrage als Quelle anstelle `tbl` in deine einbauen

 

[JoeSixpack]

Super, nach einigen Versuchen hat das geklappt! vielen Dank!

Kurze Frage noch: die Umlaute werden in JSON offenbar speziell codiert
\u00fc = ü
\u00f6 = ö
gibt es einen Befehl, diese Codierung gleich umzuwandeln?