How can I use MariaDB to pull values from multiple objects from a JSON array?

Question

In MariaDB 10.2.19, I have a table named forms with a column template which always contains a JSON array of objects. Some of these objects will have properties I want to return: name (should always be present), rule, and parameters. How can I return just these three properties from the entire array, but only for objects on which rule is present?

A sample array (formatted for easier viewing):

[{
    "label": "Employed?",
    "class": "select",
    "name": "employed",
    "parameters": "Yes",
    "rule": "in"
},
{
    "label": "Breed of dog?",
    "class": "select",
    "name": "breed",
    "parameters": "spaniel, collie, mix",
    "rule": "in"
},
{
    "label": "Number",
    "class": "text",
    "name": "breed"
}]

It would be a lot easier if you had ordinary columns, NULLable in some cases, instead of JSON. — Rick James
– Rick James, Commented Nov 20, 2018 at 16:49
@RickJames Agreed. It would be infinitely easier if the problem did not exist at all, yet it does, so here I am. — PunchParty
– PunchParty, Commented Nov 20, 2018 at 17:43

johannes · Accepted Answer · 2018-11-20 14:12:33Z

1

If you are using MySQL 8.0.4 or later one way is using JSON_TABLE:

mysql> SELECT * FROM foo;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                                                                                          |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"name": "employed", "rule": "in", "class": "select", "label": "Employed?", "parameters": "Yes"}, {"name": "breed", "rule": "in", "class": "select", "label": "Breed of dog?", "parameters": "spaniel, collie, mix"}, {"name": "breed", "class": "text", "label": "Number"}] |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> SELECT name, parameters 
         FROM foo,
              JSON_TABLE (
                  foo.data, 
                  "$[*]" COLUMNS (
                      name VARCHAR(100) PATH "$.name",
                      rule VARCHAR(100) PATH "$.rule",
                      parameters VARCHAR(100) PATH "$.parameters")
              ) AS t
       WHERE rule IS NOT NULL;
+----------+----------------------+
| name     | parameters           |
+----------+----------------------+
| employed | Yes                  |
| breed    | spaniel, collie, mix |
+----------+----------------------+
2 rows in set (0,00 sec)

https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

answered Nov 20, 2018 at 14:12

johannes

16k3 gold badges47 silver badges57 bronze badges

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

2 Comments

PunchParty Over a year ago

This would be perfect for my dilemma. Unfortunately, the database is on MariaDB 10.2.19 which does not support JSON_TABLE(). My question did not reflect this, so I have updated it accordingly.

johannes Over a year ago

Well, both systems are quite different meanwhile, mind that MariaDB also has no true JSON datatype, but only an alias to BLOB, thus not as efficient access and replication. Probably you might do something in a stored procedure using JSON_SEARCH which gives paths to all rules then some string processing and JSON_EXTRACT in a loop (Disclaimer: I work for Oracle's MySQL Team, opinions however are my own)

Ben A · Accepted Answer · 2020-09-15 18:25:10Z

You can accomplish this by using a dedicated numbers_table table:

SELECT
    JSON_VALUE(f.template,CONCAT('$[',n.number,'].name')) AS `name`,
    JSON_VALUE(f.template,CONCAT('$[',n.number,'].rule')) AS `rule`,
    JSON_VALUE(f.template,CONCAT('$[',n.number,'].parameters')) AS `parameters`
FROM forms AS f
JOIN numbers_table AS n
WHERE
    n.number < JSON_LENGTH(f.template) AND
    JSON_VALUE(f.template,CONCAT('$[',n.number,'].rule')) IS NOT NULL;

The numbers_table table contains a single column called number which starts at 0 and can be as long as your use cases require (I have 1000 values 0 to 999). It is very useful for extracting each element of a JSON_ARRAY field into its own row.

The first WHERE condition makes sure we only use as many numbers as there are elements in the JSON_ARRAY (template in your case).

The second WHERE condition eliminates the ones that don't have a rule as per your use case.

Collectives™ on Stack Overflow

How can I use MariaDB to pull values from multiple objects from a JSON array?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related