1

I have a table which saves the score reached by some students in different schools. It has 2 columns, school_name and students (type json). The table is similar to this:

school_name students
Apple Pear School [ { "name": "John", "passed": 1 }, { "name": "Mary", "passed": 1 } ]
Big Blue School [ { "name": "Bob", "passed": 1 } ]
Oak Tree Academy [ { "name": "Caty", "passed": 1 }, { "name": "Mark", "passed": 0 } ]

I want to select all rows from my table, but the students column should be filtered by the passed key, and only match those student that have a "passed": 1, so the expected result from my query should be:

school_name students
Apple Pear School [ { "name": "John", "passed": 1 }, { "name": "Mary", "passed": 1 } ]
Big Blue School [ { "name": "Bob", "passed": 1 } ]
Oak Tree Academy [ { "name": "Caty", "passed": 1 } ]

This is the query I tried so far:

SELECT 
    school_name, 
    /* Extract from the JSON array the student objects that matched the condition  */
    JSON_EXTRACT(
        students,
        /* Get only the index '$[*]' */
        TRIM(
            TRAILING '.passed'
            FROM JSON_UNQUOTE(
                /* Search the path for all students that passed exam */
                JSON_SEARCH(students, 'all', '1', null, '$[*].passed')
            )
        )
    )
FROM mytable

This query works ok when there is only one student in the json array, but if there is more than one student that match the condition, then the JSON_SEARCH() function would return an array containing the matches, and the rest of code would fail.

This is the report of my attempt:

school_name students
Apple Pear School NULL
Big Blue School { "name": "Bob", "passed": 1 }
Oak Tree Academy { "name": "Caty", "passed": 1 }

The NULL value appears because of the JSON_EXTRACT() function.

If I ommit the JSON_EXTRACT() function, and use this query:

SELECT 
    school_name, 
    /* Get only the index '$[*]' */
    TRIM(
        TRAILING '.passed'
        FROM JSON_UNQUOTE(
            /* Search the path for all students that passed exam */
            JSON_SEARCH(students, 'all', '1', null, '$[*].passed')
        )
    )
FROM mytable

The report would be this:

school_name students
Apple Pear School ["$[0].passed", "$[1].passed"]
Big Blue School $[0]
Oak Tree Academy $[0]

How can I achieve this with pure mysql, thanks in advance!

Version

SELECT VERSION()

+-----------------+
|    version()    |
+-----------------+
| 10.4.18-MariaDB |
+-----------------+
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
AlexSp3
  • 2,201
  • 2
  • 7
  • 24
  • 1
    What does `SELECT VERSION()` report? – Bill Karwin Mar 06 '22 at 18:10
  • Parse-filter-pack back. – Akina Mar 06 '22 at 18:36
  • @BillKarwin Updated with the report – AlexSp3 Mar 06 '22 at 18:48
  • I don't see where you have stated the version of MySQL you are using, which is what I asked for. The solution to do this with JSON is to use [JSON_TABLE()](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html) but that requires you have MySQL 8.0. See for example https://stackoverflow.com/a/58013321/20860 – Bill Karwin Mar 06 '22 at 19:03
  • @BillKarwin Sorry, here it is `version() 10.4.18-MariaDB` – AlexSp3 Mar 06 '22 at 19:09
  • Okay, you are using MariaDB, not MySQL. These are different products. MariaDB forked from MySQL in 2010, and both have been adding features ever since then, sometimes in incompatible ways. I don't use MariaDB, so I have no answer to offer. – Bill Karwin Mar 06 '22 at 20:41

0 Answers0