MySQL从JSON对象数组中选择字段 [英] MySQL select field from array of JSON objects

问题描述

所以我在我的数据库中有一个 column = foo_ids 其内容如下:
[{"id": "432"}, {"id": "433"}]

So i have in my database a column = foo_ids and its content is as follows:
[{"id": "432"}, {"id": "433"}]

问题是什么查询会返回这个字段中的所有 ID?

question is what query will give me back as a result all of the id's in this field?

在多次尝试中，我的最后一次是
SELECT JSON_EXTRACT(foo_ids,'$.id') FROM foo_table但显然它不起作用......知道吗?

out of several attempts my last one was
SELECT JSON_EXTRACT(foo_ids,'$.id') FROM foo_table but clearly it didn't work...any idea?

推荐答案

如果你只想返回一个 ID 数组，你可以这样做:

If you just want to return an array of the ID's you could do this:

SELECT JSON_EXTRACT(foo_ids, '$**.id') FROM foo_table;

* 通配符计算数组中的每个 json 对象.

The * wildcard evaluates each json object in an array.

这篇关于MySQL从JSON对象数组中选择字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！