Deep JSON query with partial path in MySQL 5.7?

Question

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.

You ask for this??:: SELECT JSON_EXTRACT(T.your_field, '$.l1.f1[1].c1'), JSON_EXTRACT(T.your_field, '$.l1.f1[2].c2'), ....; Full explited in MySQL docs, 11.6 The JSON Data Type — dani herrera
– dani herrera, Commented Feb 19, 2016 at 7:54

shmosel · Accepted Answer · 2016-02-21 04:42:44Z

3

+50

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;

edited Feb 21, 2016 at 4:42

answered Feb 19, 2016 at 8:07

shmosel

51.1k7 gold badges82 silver badges147 bronze badges

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

Collectives™ on Stack Overflow

Deep JSON query with partial path in MySQL 5.7?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related