Is there any way in MariaDB to search for less than value from array of json objects

Question

Here's my json doc:

[
  {
    "ID":1,
    "Label":"Price",
    "Value":399
  },
  {
    "ID":2,
    "Label":"Company",
    "Value":"Apple"
  },
  {
    "ID":2,
    "Label":"Model",
    "Value":"iPhone SE"
  },
]

Here's my table:

 +----+------------------------------------------------------------------------------------------------------------------------------------+
 | ID | Properties                                                                                                                         |
 +----+------------------------------------------------------------------------------------------------------------------------------------+
 |  1 | [{"ID":1,"Label":"Price","Value":399},{"ID":2,"Label":"Company","Value":"Apple"},{"ID":3,"Label":"Model","Value":"iPhone SE"}]     |
 |  2 | [{"ID":1,"Label":"Price","Value":499},{"ID":2,"Label":"Company","Value":"Apple"},{"ID":3,"Label":"Model","Value":"iPhone X"}]      |
 |  3 | [{"ID":1,"Label":"Price","Value":699},{"ID":2,"Label":"Company","Value":"Apple"},{"ID":3,"Label":"Model","Value":"iPhone 11"}]     |
 |  4 | [{"ID":1,"Label":"Price","Value":999},{"ID":2,"Label":"Company","Value":"Apple"},{"ID":3,"Label":"Model","Value":"iPhone 11 Pro"}] |
 +----+------------------------------------------------------------------------------------------------------------------------------------+

Here's what I want to search on search query:

  SELECT *
  FROM mobiles
  WHERE ($.Label = "Price" AND $.Value < 400)
    AND ($.Label = "Model" AND $.Value = "iPhone SE")

Above mentioned query is just for illustration purpose only. I just wanted to convey what I want to perform.

Also I know the table can be normalized into two. But this table is also a place holder table and let's just say it is going to stay the same.

I need to know if it's possible to query the given json structure for following operators: >, >=, <, <=, BETWEEN AND, IN, NOT IN, LIKE, NOT LIKE, <>

You'd need to use MySQL's [JSON_TABLE()](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html) but [MariaDB has no plans to support it](https://mariadb.com/kb/en/json_table-support/). — Bill Karwin, Dec 22 '20 at 07:09

score 1 · Accepted Answer · answered Dec 22 '20 at 07:59

Since MariaDB does not support JSON_TABLE(), and JSON_PATH supports only member/object selector, it is not so straightforward to filter JSON here. You can try this query, that tries to overcome that limitations:

with a as (
  select 1 as id, '[{"ID":1,"Label":"Price","Value":399},{"ID":2,"Label":"Company","Value":"Apple"},{"ID":3,"Label":"Model","Value":"iPhone SE"}]' as properties union all
  select 2 as id, '[{"ID":1,"Label":"Price","Value":499},{"ID":2,"Label":"Company","Value":"Apple"},{"ID":3,"Label":"Model","Value":"iPhone X"}]' as properties union all
  select 3 as id, '[{"ID":1,"Label":"Price","Value":699},{"ID":2,"Label":"Company","Value":"Apple"},{"ID":3,"Label":"Model","Value":"iPhone 11"}]' as properties union all
  select 4 as id, '[{"ID":1,"Label":"Price","Value":999},{"ID":2,"Label":"Company","Value":"Apple"},{"ID":3,"Label":"Model","Value":"iPhone 11 Pro"}]' as properties
)
select *
from a
where json_value(a.properties,
    /*Get path to Price property and replace property name to Value*/
    replace(replace(json_search(a.properties, 'one', 'Price'), '"', ''), 'Label', 'Value')
  ) < 400
  and json_value(a.properties,
    /*And the same for Model name*/
    replace(replace(json_search(a.properties, 'one', 'Model'), '"', ''), 'Label', 'Value')
  ) = "iPhone SE"

| id | properties
+----+------------
| 1  | [{"ID":1,"Label":"Price","Value":399},{"ID":2,"Label":"Company","Value":"Apple"},{"ID":3,"Label":"Model","Value":"iPhone SE"}]

db<>fiddle here.

score 1 · Answer 2 · answered Dec 22 '20 at 15:13

I would not use string functions. What is missing in MariaDB is the ability to unnest the array to rows - but it has all the JSON accessors we need to access to the data. Using these methods rather than string methods avoids edge cases, for example when the values contain embedded double quotes.

You would typically unnest the array with the help of a table of numbers that has at least as many rows as there are elements in the biggest array. One method to generate that on the fly is row_number() against a table with sufficient rows - say sometable.

You can unnest the arrays as follows:

select t.id,
    json_unquote(json_extract(t.properties, concat('$[', n.rn, '].Label'))) as label,
    json_unquote(json_extract(t.properties, concat('$[', n.rn, '].Value'))) as value
from mytable t
inner join (select row_number() over() - 1 as rn from sometable) n
    on n.rn < json_length(t.properties)

The rest is just aggregation:

select t.id
from (
    select t.id,
        json_unquote(json_extract(t.properties, concat('$[', n.rn, '].Label'))) as label,
        json_unquote(json_extract(t.properties, concat('$[', n.rn, '].Value'))) as value
    from mytable t
    inner join (select row_number() over() - 1 as rn from sometable) n
        on n.rn < json_length(t.properties)
) t
group by id
having 
    max(label = 'Price' and value + 0 < 400) = 1 
    and max(label = 'Model' and value = 'iPhone SE') = 1

Demo on DB Fiddle

Is there any way in MariaDB to search for less than value from array of json objects

2 Answers2