[MySQL] Resultaten ophalen aan de hand van JSON array

[Av-]

Beste,

Ik ben momenteel bezig met een kleding webwinkel en ben op het volgende probleem gestuit.
De artikelen worden met o.a. de volgende velden in de DB opgeslagen.
ID (int), Title (Varchar), Prijs (Float), Data (text)
Nu zijn de velden id, title en prijs niet zo boeiend, bij het veld Data wordt het pas interessant.
In het Data veld worden bijvoorbeeld de beschikbare maten en washing van een spijkerbroek opgeslagen. Deze gegevens worden omgezet naar een JSON string, en opgeslagen in het Data veld. De gebruiker moet dan weer d.m.v. een formulier de resultaten kunnen filteren a.d.h.v. bijv. de maat, washing of kleur.

Kort samengevat is dit mijn MySQL tabel.

id	title	prijs	data
1	Spijkerbroek	60.00	{"Maat":["32/34", "34/34", "34/36"], "Wash":["Light Denim", "Dark Denim", "Stonewashed"]}
2	T-Shirt	20.00	{"Maat":["M", "L", "XL"], "Kleur":["Rood", "Blauw", "Groen", "Geel"]}
3	Schoen	80.00	{"Maat":["32", "33", "34"]}

Waar het dus in principe op neerkomt is dat deze query werkend moet
"SELECT id, title, prijs WHERE title = "Spijkerbroek" AND data[Maat] = '32/34' AND data[Wash] = 'Light Denim' etc. etc."
Nu gaat dit natuurlijk niet werken, maar hopelijk legt dit uit waar ik naar toe wil.

Ik heb rondgezocht en voor zover ik kon vinden kan MySQL praktisch niets met JSON. Deze data array zou wellicht ook in XML of serialized opgeslagen kunenn worden als MySQL daar wel wat mee kan.

Enig idee op welke manier dit werkend te krijgen is?

Tnx

[Manuel]

Ik denk toch echt dat het verstandiger is om te gaan normaliseren, dat scheelt je dan namelijk heel wat werk achteraf. Als dat echt geen optie is kun je eventueel kijken naar http://blog.hungrymachine...ng-a-json-encoded-string/ en dan mag je hopen dat het goed werkt.

[ Voor 44% gewijzigd door Manuel op 31-01-2011 14:21 ]

[RobIII]

Woa, ik zie nog wel een comma separated zaken in een tabel, maar JSON ben ik nog niet tegengekomen (wel XML overigens ).

Weet je wat jij moet doen? Je moet je eens even inlezen in normaliseren en daarna eens gaan kijken of je dit in een genormaliseerd ontwerp kunt opslaan. Lukt dat niet dan kun je eens gaan kijken naar de zgn. "NoSQL" oplossingen als MongoDB, CouchDB en consorten.

[ Voor 12% gewijzigd door RobIII op 31-01-2011 14:21 ]

[NMe]

Er is een hele goede reden dat MySQL niks met JSON doet: je kan het niet queryen. Een database in een database werkt niet, en comma separated fields, wat dit in feite is, hebben ook bijna alleen maar nadelen, vooral in performance.

Manuel zegt het nog redelijk vrijblijvend, maar dit soort dingen moet je gewoon normaliseren. Anders is je webwinkel gewoon totaal onbruikbaar zodra je wat meer producten en/of bezoekers krijgt.

edit:
RobIII, jij vuile tussenpostert.

[ Voor 5% gewijzigd door NMe op 31-01-2011 14:22 ]

[Av-]

Deze filter-opties verschillen per categorie waar het artikel onder valt en is dus niet te normaliseren tenzij ik er een hoop filter-opties uit haal. Wat ook een optie is, is dat ik voor elke categorie een apart MySQL tabel aanmaak, met de desbetreffende velden. Nadeel hiervan is dat er een hoop tijd wordt verspilt voor het aanmaken van de tabellen en dit niet optimaal werkt i.c.m. het CMS wat wij gebruiken.

In ieder gevalt bedankt voor de tips dusver. Ik ga me even inlezen.

[ Voor 7% gewijzigd door Av- op 31-01-2011 14:44 ]

[NMe]

Het aantal opties betekent juist dat je moet normaliseren.

[MueR]

NMe schreef op maandag 31 januari 2011 @ 14:21:
Een database in een database werkt niet

Daar zijn uitzonderingen op overigens Waar geen uitzondering op is, is het advies om nooit een database in een database te gebruiken tenzijn je echt geen alternatief hebt.

[_js_]

Naast het normaliseren (waar trouwens geen waslijst aan tabellen voor nodig is), moet je misschien ook even naar prijs kijken, gebruik daar geen float, maar een decimal o.i.d., met floats kun je afrondingsfouten krijgen.

[NMe]

_js_ schreef op maandag 31 januari 2011 @ 15:13:
Naast het normaliseren (waar trouwens geen waslijst aan tabellen voor nodig is), moet je misschien ook even naar prijs kijken, gebruik daar geen float, maar een decimal o.i.d., met floats kun je krijg je afrondingsfouten krijgen.

Fixed that for you.

[Av-]

NMe schreef op maandag 31 januari 2011 @ 15:08:
Het aantal opties betekent juist dat je moet normaliseren.

Kun je dat uitleggen?

Verder wil ik even toelichten dat alle beschikbare opties al elders worden opgeslagen, dus wat betreft is het al enigszins genormaliseerd. Het gaat hier om de opties die voor het betreffende artikel zijn geselecteerd, die moeten natuurlijk ook ergens worden opgeslagen, ik zie niet hoe dit anders kan dan in de rij van het artikel zelf (of dit nou csv, json of xml is doet er verder niet toe).

En bedankt voor de tip voor het gebruiken van decimal i.p.v. float. Ik ga het doorgeven.

[ Voor 40% gewijzigd door Av- op 31-01-2011 16:36 . Reden: toelichting ]

[jbdeiman]

@Av
Ik kan denk ik wel uitleggen wat er word bedoeld:
- Het aantal opties en de opties zelf verschillen per categorie. Wat je nu gaat doen is verschillende (typen) data met elkaar vergelijken en dit gaat problemen opleveren.

Normaliseren maakt de onderhoudbaarheid ook veel eenvoudiger, wat nu als je bij alle truien een nieuwe optie "stofsoort" toe wilt voegen? Hoe ga je dat dan doen met je json string? Lijkt me geen pretje voor degene die dit moet onderhouden.

Normaliseren kan je als volgt nog wel doen:
- Tabel met eigenschappen (maat/ kleur/ motief/ stof/ wash)
- Tabel met de categorieën
- Tabel met de koppeling tussen categorie en eigenschap die je daarbij wilt hebben.
- Helemaal netjes als je nog een tabel met waarden hebt (S/ M/ L/ XL/ XXL e.d. zijn standaard gegevens, deze kan je in een maten tabelletje bijhouden, leuke bijkomstigheid is dat je dan ook nog meer gegevens kan koppelen aan een maat, of aan een kleur.

Voordeel is dat je heel eenvoudig eigenschappen kan toevoegen en weg kan halen zonder moeilijk te doen. Daarbij wordt zoeken ook een stuk eenvoudiger, en net zo belangrijk: sneller. Dat je als je in de database kijkt wat heen en weer moet zoeken om iets uit te zoeken is niet erg, een simpele query haalt dit al goed voor je op. Gaat heel snel en je hoeft niet ingewikkeld te doen met allerlei zaken, waar je veel tijd en moeite in steekt om dan toch te concluderen dat het beter had gekunt.

[Tiemez]

weet niet wat ik erger vind...json in een veld of een veld met de naam "data"

[Av-]

Tiemez schreef op maandag 31 januari 2011 @ 17:43:
weet niet wat ik erger vind...json in een veld of een veld met de naam "data"

Veldnamen zijn illustratief en verder niet relevant. Daarnaast hoor ik het graag als jij een betere suggestie hebt om deze gegevens op te slaan.

jbdeiman schreef op maandag 31 januari 2011 @ 16:39:
@Av
Normaliseren kan je als volgt nog wel doen:
- Tabel met eigenschappen (maat/ kleur/ motief/ stof/ wash)
- Tabel met de categorieën
- Tabel met de koppeling tussen categorie en eigenschap die je daarbij wilt hebben.
- Helemaal netjes als je nog een tabel met waarden hebt (S/ M/ L/ XL/ XXL e.d.

Wellicht heb ik de opbouw in mijn openingspost niet helemaal helder uitgelegd, maar al deze gegevens zijn uiteraard al opgedeeld in verschillende tabellen.
Misschien dat een simpel schema het geheel wat duidelijker maakt.


In het kort, eigenschappen (filter-opties) worden in de back-end aangemaakt, Categorieën worden aangemaakt en gekoppeld aan relevante eigenschappen. Winkelier logt in op CMS en voegt artikel toe, aan de hand van de categorie waaronder het artikel valt worden de eigenschappen opgehaald en de winkelier kan aanvinken wat van toepassing is. DEZE data moet opgeslagen worden en te filteren zijn.

[messi]

Of je zou eens naar MongoDB kunnen kijken, die kan dit soort dingen wel prima aan. En je kan ook makkelijk querien op dingen in de JSON (bijv. kleding.maat: '32' )

[YopY]

Av- schreef op maandag 31 januari 2011 @ 14:41:
Deze filter-opties verschillen per categorie waar het artikel onder valt en is dus niet te normaliseren tenzij ik er een hoop filter-opties uit haal.

[...]

Het gaat hier om de opties die voor het betreffende artikel zijn geselecteerd, die moeten natuurlijk ook ergens worden opgeslagen

Natuurlijk is het wel te normaliseren, alles is te normaliseren. Als er teveel variabele gegevens zijn, maak je gewoon een variabelentabel:

code:

id    titel
===============
1     Maat
2     Wash

En een koppeltabel die een variabele aan zowel een product als een waarde koppelt (ervanuitgaande dat de waarden volledig random kunnen zijn)

code:

id       var_id    product_id    value
===============================
1        1           1337            "32/34"
2        1           1337            "34/34"
3        1           1337            "34/36"
4        2           1337             "Light Denim"

En een derde tabel die een keuze voor zo'n variabele van een product bij een order van een product schaart:

code:

order_id    order_item    selected_var
==============================
12             3                 1
12             3                 4
734           6                 3

met natuurlijk een tabel die een volledige order bevat, elk product in de order krijgt een uniek ID (danwel een samengesteld ID van order + item ID)

(noot: bovenstaand kan prima met samengestelde ID's, is slechts een voorbeeld)

(noot 2: [/voorkauw])

(edit: * YopY steelt antwoord . Misschien een idee om 'live' mensen die replyen te kunnen tonen, danwel om posts te tonen terwijl ze getypt worden? Next-gen web 3.0 forumsoftware ftw.

[ Voor 5% gewijzigd door YopY op 01-02-2011 12:45 ]

[FragFrog]

Av- schreef op maandag 31 januari 2011 @ 18:29:
DEZE data moet opgeslagen worden en te filteren zijn.

En dat kun je uitstekend doen op de manier die jbdeiman voorstelt, op bovendien zo'n manier dat je er wel op kan querien

Stel je tabel "eigenschappen" heeft de records "maat", "wash", "kleur". Een andere tabel, "waarden", bevat dan bijvoorbeeld de records "rood", "Light Denim", "32/34", etc. Kennelijk bestaan deze tabellen al in je systeem wat het geheel alleen maar makkelijker maakt.

Wat je nu kan doen is een koppeltabel maken die eigenschappen en hun waarde toekent aan een product. Dat kan een tabel zijn met bijvoorbeeld "productID", "eigenschapID", "waardeID". Als een broek meerdere opties heeft voor bijvoorbeeld kleur krijg je ook meerdere entries in die tabel ("broek A", "kleur", "Rood" - maar dan met ID's in plaats van strings).

Dan kun je bij het zoeken naar bijvoorbeeld alle producten die in het rood of blauw leverbaar zijn een query schrijven als:

SQL:

SELECT    `Title`
FROM      `product`
JOIN      `productEigenschap`
  ON      `product`.`ID`                      = `productEigenschap`.`productID`
 AND      `productEigenschap`.`eigenschapID`  = 1
WHERE     `productEigenschap`.`waardeID`      = 3
  OR      `productEigenschap`.`waardeID`      = 7

(Uit de losse pols, misschien is het in de praktijk handiger een LEFT JOIN en GROUP BY te gebruiken, maar het principe is denk ik wel duidelijk)

Hoe je aan de ID's komt voor de verschillende eigenschappen en hun waardes is implementatie-afhankelijk, maar je zou bijvoorbeeld op je zoekpagina een formulier kunnen maken waarin je simpelweg deze ID's gebruikt en weer uitleest uit je GET request. Alternatief kun je ze ook vrij simpel uit je originele tabel halen als je alleen de naam hebt met bijvoorbeeld een subquery of losse query vooraf, dat is niet bijster spannend verder

//edit
Te laat, dankje YopY..

[RobIII]

Het wordt vervelender als je niet met (alleen maar) voorgedefinieerde waarden te maken hebt. Dan heb je bijvoorbeeld de eigenschappen stroomverbruik, pagina's per minuut, productiedatum of whatever en dan gaan 'zoeken' op een apparaat < 80 Watt met > 20ppm gefabriceerd na jul. 2010 wordt dan wat lastig omdat de values dan of voorgedefinieerd moeten zijn (en dus huge-ass "dropdowns" bij bijv. wattage of productiedatum) of als "string" worden opgeslagen in de koppeltabel omdat je allerlei types in eenzelfde tabel probeert te proppen. Er zijn 'tussenoplossingen' maar dan kun je beter even wat tijd steken in een NoSQL langs je RDBM's voor het queryen ervan.

[Av-]

Bedankt voor alle reacties en suggesties. Ik begrijp het concept en ga het morgen voorleggen.

[Av-]

Om nog even terug te komen op dit topic, zodat anderen die hier naar zoeken hier wellicht ook wat aan hebben.
Mijn collega heeft in de tussentijd ook even rond gezocht en vond dat MySQL wel degelijk een aantal functies m.b.t. XML heeft.
http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue
Wij gaan voor dit project, om hier niet nog meer tijd aan te spenderen, dus door met de huidige opzet, maar vervangen de JSON voor XML.
In ieder geval toch bedankt voor de tips en suggesties, wij gaan dit zeker meenemen voor toekomstige projecten.

[Jaap-Jan]

Please, don't! RobIII raadt het zelfs al af in dit topic!

[ReenL]

1) Het XML Dataformaat is trager dan JSON en dat werd al afgeraden;
2) Normaliseer je data, zoals eerder gezegd, dan kun je namelijk "normale" queries uitvoeren;
3) Er komt een dag dat je hier spijt van krijgt, omdat de performance om te huilen wordt;
4) Dat iets kan betekend niet dat het goed is, je kan bijvoorbeeld met MySQL dit doen:

code:

SELECT * FROM table WHERE DATE_ADD(datefield, INTERVAL 1 DAY) > NOW();

Daarmee voer je een berekening uit voor elke ROW (iets wat jou XML Lib ook gaat doen). Dit betekent dat voor elke row die toegevoegd wordt, je applicatie langzamer wordt.

[Bender]

RobIII schreef op maandag 31 januari 2011 @ 14:19:
Woa, ik zie nog wel een comma separated zaken in een tabel, maar JSON ben ik nog niet tegengekomen (wel XML overigens ).

Ik wel, normaliseren heeft niet altijd zin en soms zelfs performance verlies.
Als je tussen de 10 en 25 fotos bij een broek bijvoorbeeld wilt plaatsen heb je 3 opties;
- Lekker extra tabelletje aanmaken met alleen regels met foto's, extra queries dus
- 25 extra velden aanmaken, die niet altijd gebruikt worden
- csv of json in 1 veld opslaan en nooit beperking hebben

Ik zou zeker niet de eerste optie kiezen, je levert daarmee direct performance in omdat je extra kan gaan querien maar levert je geen voordelen op.
Tenzij je er op wil gaan querieen natuurlijk, dan wordt het een ander verhaal

[CodeCaster]

ReenL schreef op woensdag 02 februari 2011 @ 19:47:
4) Dat iets kan betekend niet dat het goed is, je kan bijvoorbeeld met MySQL dit doen:

code:

SELECT * FROM table WHERE DATE_ADD(datefield, INTERVAL 1 DAY) > NOW();

Daarmee voer je een berekening uit voor elke ROW (iets wat jou XML Lib ook gaat doen). Dit betekent dat voor elke row die toegevoegd wordt, je applicatie langzamer wordt.

offtopic:
Ik neem aan dat er met een index op datefield en een vereenvoudiging door de SQL engine meer een query als "WHERE datefield > DATE_ADD(NOW(), INTERVAL -1 DAY)" uitgevoerd gaat worden die niet per se trager wordt per toegevoegd record.

[ Voor 3% gewijzigd door CodeCaster op 03-02-2011 00:17 ]

[RobIII]

Bender schreef op donderdag 03 februari 2011 @ 00:10:
Ik wel, normaliseren heeft niet altijd zin en soms zelfs performance verlies.

Een kleine mate van denormalisatie kan soms handig zijn; dat is per case te beschouwen. Het uitgangspunt dient echter te zijn dat 't niet nodig is.

Bender schreef op donderdag 03 februari 2011 @ 00:10:
Als je tussen de 10 en 25 fotos bij een broek bijvoorbeeld wilt plaatsen heb je 3 opties;
- Lekker extra tabelletje aanmaken met alleen regels met foto's, extra queries dus

Poehee. Extra queries. Stel je voor dat je RDBMS eens moet werken voor z'n geld. Met de juiste indexen spuugt 'ie dat zo uit hoor. En je krijgt er de flexibiliteit bij dat je alles tussen 0 en heleboel foto's kwijt kunt per 'parent record'. De enige aanpassing die je dan moet doen als je de limiet wil wijzigen is in je BL waar je de 25 afdwingt (als je dat al doet); en als je slim bent staat het daadwerkelijke aantal dan in een config oid en niet hardcoded.

Bender schreef op donderdag 03 februari 2011 @ 00:10:
- 25 extra velden aanmaken, die niet altijd gebruikt worden

Dat is in 99.99% van de gevallen inderdaad niet echt wenselijk; zeldzame uitzonderingen daargelaten.

Bender schreef op donderdag 03 februari 2011 @ 00:10:
- csv of json in 1 veld opslaan en nooit beperking hebben

Oh jij noemt 't volgende geen beperking?

• Dramatisch performance verlies wanneer je iets met de inhoud van dat veld wil gaan doen (moet je XML, CSV of JSON functies van het RDBMS gaan aanspreken, als ze er al zijn); en dan maak jij je druk over een extra query
• Max. X aantal tekens voor je JSON, CSV, XML omdat je 't in een varchar stopt waardoor je inhoud niet opgeslagen wordt als de lengte het max. aantal tekens zal overschrijden of erger: het wordt afgekapt. Durf je er een text/memo veld van te maken om die beperking "op te heffen" dan kun je opeens geen "group by" meer gebruiken in de meeste RDBMS'en om maar eens wat te noemen. En werk je daaromheen dan kom je 't volgende probleem wel weer tegen.
• Totaal verlies van referentiële integriteit
• Wanneer je een record uit de DB haalt mag je vervolgens in je BL spontaan een veld gaan deserializen, parsen, whatever om de gerelateerde data te 'begrijpen' of kunnen gebruiken. Zeg maar tegen ORM.
• ...ga zo maar door

Bender schreef op donderdag 03 februari 2011 @ 00:10:
Ik zou zeker niet de eerste optie kiezen, je levert daarmee direct performance in omdat je extra kan gaan querien maar levert je geen voordelen op.

Ga alsjeblieft eens even wat inlezen in de materie; je doet net of een "extra query" het einde van de wereld is. Er zijn echt wel gevallen waar een extra query misschien ongewenst is, maar dan heb je al een heel ander probleem in een heel andere orde van grootte. Als je denkt dat JSON, CSV of XML te verdedigen is i.p.v. een 1:N tabel omwille van performance dan heb je het grondig mis.

Bender schreef op donderdag 03 februari 2011 @ 00:10:
Tenzij je er op wil gaan querieen natuurlijk, dan wordt het een ander verhaal

En tenzij je 1 van de andere punten die ik net aanhaal ook niet wil ervaren.

Zoals ik al eerder zei en ook al door anderen is aangekaart: als de data wat te "flexibel" is dan gebruik je gewoon een RDBMS voor het opslaan van de gedeelde eigenschappen van de producten (id, title, prijs) en voor de rest gebruik je "een NoSQL". Daar maak je een product tabel waar je hetzelfde ID als het ID van het product in het RDBMS gebruikt om andere data als maat, wash, kleur enz aan te hangen. Voila. En zo kun je bijvoorbeeld, als je wil, een voorgedefinieerde lijst met maten (S, M, L, XL, XXL) wel prima in je RDBMS parkeren zodat gebruikers bij het invoeren van een product enkel mogen kiezen uit die waardes maar die waardes vervolgens in "de NoSQL" gewoon opslaan als waarde van een bepaalde eigenschap. Het enige beetje werk dat je in zo'n geval misschien hebt is dat je een klein laagje "lijm" moet schrijven om (bijv.) bij het verwijderen van bijv. maat "XL" uit de "toegestane maten tabel" de NoSQL ook bij te werken door alle producten met die maat van een nieuwe of lege waarde te voorzien of heel 't attribuut te verwijderen. Met een beetje net abstractielaagje stelt 't geen drol voor en heb je niet over 2 maanden (en dat geef ik je op een briefje) een moment als je voor de honderdduizendste keer met je JSON uit een DB zit te vechten in je queries danwel BL danwel...

Dit soort 'hybride' oplossingen (RDBMS+NoSQL) zie je steeds meer. Afhankelijk van de uitgangssituatie en andere factoren kun je natuurlijk ook kiezen voor een 100% NoSQL oplossing te gaan. Then again, een 100% RDBMS oplosssing is net zo goed mogelijk maar wat meer werk (en nou net de reden van dit topic) als je zo flexibel wil zijn als TS als je 't goed wil normaliseren e.d.

[ Voor 6% gewijzigd door RobIII op 03-02-2011 02:44 ]

[creator1988]

Funda heeft ook een shitload aan categorieën waarop gequeried kan worden, en wij redden het ook prima zonder JSON in 'data' velden. Check dit artikel: Coding Glamour: Solr, deel 1: Introductie tot faceted search.