Search on Json with patterns (MYSQL Regex)

Question

i having problems with a proyect... it's like a experiment or idk... It's a Relational database with Json on it like a response of a form

It's like

Select details from mytable where id = 1

and i get something like this

{
   "altitud":"1253,m.s.n.m",
   "tipo_vacacion":[
      "1"
   ],
   "ubicacion":{
      "scalar":"1"
   },
   "area":{
      "scalar":"800"
   },
   "huespedes":{
      "scalar":"4"
   },
   "acompanamiento":{
      "scalar":"1"
   },
   "departamento":{
      "scalar":"26"
   },
   "municipio":{
      "scalar":"Socorro"
   },
   "direccion":{
      "scalar":"Carrera....Calle"
   },
   "encargado":{
      "scalar":"Hernando Marquez"
   },
   "encargado_telf":{
      "scalar":"3158895585"
   },
   "traslado_km":{
      "scalar":"0"
   },
   "traslado_tiempo":{
      "scalar":""
   },
   "acceso_inmueble":{
      "0":"0"
   },
   "clima":{
      "scalar":"1"
   },
   "servicios":{
      "0":"0",
      "1":"1",
      "2":"2",
      "3":"4",
      "4":"7",
      "5":"8"
   },
   "actividades":{
      "scalar":"Cocina, Hamacas, Caminatas por el jard\u00edn, Retiros de Silencio."
   },
   "cultura":{
      "scalar":"Museo Casa de la Cultura del Socorro.\r\nTeatro Municipal, Catedral."
   },
   "ecodestinos":{
      "0":"1",
      "1":"10",
      "2":"11"
   },
   "cobertura_celular":{
      "0":"0",
      "1":"1",
      "2":"2",
      "3":"3"
   },
   "seguridad":{
      "0":"41",
      "1":"41",
      "2":"41",
      "3":"41",
      "4":"41"
   },
   "matricula":{
      "scalar":"000000000000"
   },
   "una_semana":{
      "scalar":"0"
   },
   "medio_mes":{
      "scalar":"1"
   },
   "recicla":{
      "0":"0",
      "1":"1",
      "2":"2",
      "3":"3"
   },
   "energia_limpia":{
      "scalar":"en Complementaci\u00f3n."
   },
   "manejo_aguas":{
      "scalar":"En an\u00e1lisis."
   }
}

They want me to create a "like" query to know something like

Select id from mytable where details like '"servicios":%"1":"1"%"2":"2"%"3":"4"%"4":"7"'

(That query its not working by the way...)

It looks simple at first but, do you notice that "scalar" thing? and sometimes they say to me: That element it's not on the value, its on the key, but we are not sure where is it normally so, you need to show us both

Thats why i come here, cuz i spend 1 week trying (i solved that problem getting all the table and creating a function on php but now they want it with mysql...)

Please, help me

No, its not, just improvement of a query they do... a homework of course it's much more easier than this — GunBlade
– GunBlade, Commented Jan 27, 2014 at 2:29

redolent · Accepted Answer · 2014-01-27 05:11:11Z

1

Try this:

SELECT * FROM `mytable` 
WHERE  `details`  REGEXP  
'"servicios":\{[ [.newline.]]*"0":"0",[ [.newline.]]*"1":"1",[ [.newline.]]*"2":"2",[ [.newline.]]*"3":"4",[ [.newline.]]*"4":"7",[ [.newline.]]*"5":"8"[ [.newline.]]*\}'

http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

edited Jan 27, 2014 at 5:11

answered Jan 27, 2014 at 0:57

redolent

4,2895 gold badges41 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

GunBlade Over a year ago

I get an error 00:06:08 SELECT * FROM mytable WHERE details REGEXP '"servicios":{[ [.newline]]*"0":"0",[ [.newline]]*"1":"1",[ [.newline]]*"2":"2",[ [.newline]]*"3":"4",[ [.newline]]*"4":"7",[ [.newline]]*"5":"8"[ [.newline]]*}' LIMIT 0, 1000 Error Code: 1139. Got error 'brackets ([ ]) not balanced' from regexp 0.140 sec

redolent Over a year ago

Just escape the brackets. Instead of { or } do \{ and \} I'll edit my answer shortly.

GunBlade Over a year ago

SELECT * FROM mytable WHERE details REGEXP '"servicios":{\[ \[.newline\]\]*"0":"0",\[ \[.newline\]\]*"1":"1",\[ \[.newline\]\]*"2":"2",\[ \[.newline\]\]*"3":"4",\[ \[.newline\]\]*"4":"7",\[ \[.newline\]\]*"5":"8"\[ \[.newline\]\]*}' returns 0 rows :S

GunBlade Over a year ago

Yes, it works but its not exactly what i need... it was too useful by the way so i could start to try my own patterns

Collectives™ on Stack Overflow

Search on Json with patterns (MYSQL Regex)

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related