如何在MySQL查询本身中检索存储在JSON数组中的值? [英] How to retrieve values stored in JSON array in MySQL query itself?

问题描述

我有下表

product_id    product_name    image_path                             misc
----------   --------------  ------------                           ------
     1            flex        http://firstpl...      {"course_level_id":19,"group_id":"40067"}
     2           Android      http://firstpl...      {"course_level_id":20,"group_id":"40072"}

那么我该如何检索product_name，image_path& "misc"列中只有"group_id"值，例如"40067".

So how can i retrieve the product_name,image_path & only "group_id" value like "40067" from "misc" column.

我在下面的查询中尝试过，但是它在杂项"列中返回1/0.

I tried below query but it returning 1/0 in Misc column.

SELECT product_name,image_path,misc REGEXP '(.*\"group_id\":*)' as Misc FROM ref_products where product_id=1

任何有创意的人怎么做?

Any idea guys how to do it ?

推荐答案

REGEXP函数仅返回0或1.您将不得不使用其他字符串函数.

The REGEXP function just returns 0 or 1. You will have to use other string functions.

尝试以下操作:substr(misc,locate('group_id',misc)+11,5) as Misc.但这假设group_id始终有5个字符.

Try this: substr(misc,locate('group_id',misc)+11,5) as Misc. But that assumes that group_id always has 5 characters.

所以更好:substring_index(substr(misc,locate('group_id',misc)+char_length('group_id')+3),'"',1) as Misc.

这里有一个小提琴来说明它的工作原理: http://sqlfiddle.com/#!2 /ea02e/15

Here is a fiddle to show it working: http://sqlfiddle.com/#!2/ea02e/15

编辑，您可以通过在字符串中包含双引号和冒号来消除+3魔术数字: substring_index(substr(misc,locate('"group_id":"',misc)+char_length('"group_id":"')),'"',1) as Misc

EDIT You can get rid of the +3 magic number by including the double quotes and colon in the strings like this: substring_index(substr(misc,locate('"group_id":"',misc)+char_length('"group_id":"')),'"',1) as Misc

这篇关于如何在MySQL查询本身中检索存储在JSON数组中的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！