MySQL JSON - group by JSON array index

Question

I'm trying to count number of nested JSON array elements grouped by parent index using MySQL 8 JSON type field. My JSON string looks like

{
  "a": [
    {
      "b": [
        1,
        2,
        3
      ]
    },
    {
      "b": [
        1
      ]
    }
  ]
}

I'm trying to get the count of elements under "b" key for each "a" element. I need an output similar to:

{0: 3, 1: 1}

Meaning that a[0] has 3 elements under "b", while a[1] has 1.

This query counts total number of "b" elements across all "a"s (yields 4):

select JSON_LENGTH(json->>'$.a[*].b[*]') from myTable

Is it possible to somehow group it by a's index?

Thank you!

wchiquito · Accepted Answer · 2018-12-23 20:58:49Z

3

One option is JSON_TABLE and JSON_OBJECTAGG:

SELECT
  JSON_OBJECTAGG(
    `rowid` - 1,
    JSON_LENGTH(`count`)
  )
FROM JSON_TABLE(
  '{"a":[{"b":[1,2,3]},{"b":[1]}]}',
  '$.a[*]'
  COLUMNS(
    `rowid` FOR ORDINALITY,
    `count` JSON PATH '$.b'
  )
) `der`;

See db-fiddle.

edited Dec 23, 2018 at 20:58

answered Dec 23, 2018 at 20:50

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

MySQL JSON - group by JSON array index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related