mysql 查询 json 数据 [英] mysql query on json data

问题描述

我的 mysql 表中有一个 param 字段，其中包含 json 数据.编码后看起来像这样:

I have a param field in my mysql table what's contains json data. After encode looks like this:

  'config.enable_comments' => string '1' 
  'metadata.description' => string '' 
  'metadata.keywords' => string '' 
  'metadata.robots' => string '' 
  'metadata.author' => string '' 
  'config.primary_category' => string '157' 

我如何根据 config.primary_category 进行 mysql 查询.

how can i make a mysql query depending for example on config.primary_category.

SELECT params
FROM #__zoo_item
WHERE  config.primary_category = ". $id;

推荐答案

一年多以后...我需要对一个由于某种原因无法对数据库进行规范化的项目进行此类查询.

And over a year later... I needed to do this kind of query with a project where the database can not be normalized for some reason.

这是一种可能的解决方案:

This is one possible solution:

SELECT * 
FROM myTable 
WHERE if(
  instr(myField,'myJsonField'),
  substring(
    substring(myField,instr(myField,'myJsonField')+15),
    1,
    instr(substring(myField,instr(myField,'myJsonField')+15),'\",\"')-1
  ),
  ''
) = 'theJsonValue'

请注意'myJsonField'的长度为11，加上JSON格式的分隔符号为15.

Please note that 'myJsonField' has a length of 11, adding to that the symbols of separation of JSON format is 15.

这篇关于mysql 查询 json 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！