TIL: regex în mysql

Printr-o alinere extrem de nasoală a planetelor, împreună cu zeița bazelor de date supărată, am nimerit într-o situație foarte mișto. Pe scurt: versiune veche a DB. Mai pe lung:

Datorită structurii, cea mai bună soluție pentru a stoca niște documente în DB mi s-a părut a fi ceva ne-relațional. Mă uit în manualul MySQL: suportă JSON de ceva timp. Perfect! Pe local am instalat ultima dată MariaDB acum vreo 1-2 ani, deci e OK. Pe server nu mai verific, că sigur nu stau ăștia cu versiunile atât de vechi.

Mă apuc, fac queries cu JSON, dezvolt tot ce trebuie, fac deploy. Pe server avem Maria DB 10.0. Ultimul release în 2015. EVIDENT că are fix zero support pentru JSON!

Deci sunt într-o dilemă: ori conving clientul să renunțe la provider ori migrez datele pe ceva relațional. Deci am relaționat datele, că de fapt asta era singura opțiune. Și am făcut asta în doi pași (mai simpli decât mă așteptam):

1. Am făcut o tabelă documents ce conține minimum de coloane: id, title, abstract, content.
2. Am făcut o tabelă document_meta ce conține patru coloane: id, document_id, meta_key, meta_value.

Eh, iar șmecheria abia acum începe: dacă pentru majoritatea datelor structura asta este OK, aveam un element mai … deosebit: geography. Care este un array multidimensional de forma:

[
  {"state": "Colorado", "province": "Aspen"},
  ....
]

Provocarea era să număr toate documentele specifice unui anumit stat (sau provincie) și să le ordonez după acest număr. După căutări și multă lectură, am reușit treaba asta:

SELECT REGEXP_REPLACE( 
    REGEXP_SUBSTR(meta_value, '"state":"[^"]+"'), 
    '"state":"([^\"]+)"' , 
    '\\1' ) as state, COUNT(*) as count
FROM documents doc
    INNER JOIN document_meta meta ON meta.document_id = doc.id
WHERE
    meta.meta_key = 'geography'
GROUP BY state
ORDER BY count DESC

E prima dată când lucrez cu regex în SQL iar senzatia este… ciudată. N-aș putea spune că mi-a plăcut în mod deosebit sau că a fost din cale-afară de intuitiv.

Mi s-a readus aminte că niciodată nu trebuie să fac presupuneri despre mediul în care rulez ceva. Mai ales dacă nu am nici un fel de control asupra acelui mediu.

All in all, a fost o zi bună, am învățat chestii. Fu bine.