mysql 5.7多层级json查询,MySQL 5.7中具有部分路径的深度JSON查询？

Given a number of JSON document like this:

{

id: some_id,

l1: {

f1: [

{

c1: foo,

c2: bar

},

{

c1: foo1,

c2: bar1

}

],

f2: [

{

c3: baz,

c4: bar

}

]

}

}

How can I query MySQL 5.7 for f1....c1: foo1 -- ie lX is not given nor is the list position of the c1-c2 subdocument.

This is not a duplicate of Deep JSON query with partial path in PGSQL JSONB? since that is about PostgreSQL and this one is about MySQL.

解决方案

This should do it:

SELECT JSON_CONTAINS(JSON_EXTRACT(Doc, '$.*.f1[*].c1'), '"foo1"') FROM table;

If you're using 5.7.9 or later, you can replace the JSON_EXTRACT function with the -> operator:

SELECT JSON_CONTAINS(Doc->'$.*.f1[*].c1', '"foo1"') FROM table;