Reading array json - memsql

Question

Reading array json - memsql

I have array where items is a json.

Table:

CREATE TABLE `example`  (
orderId BIGINT,
`data`  JSON NULL
);

Example record

orderId -> ZA/XYZ
data -> [
{'item':1,'price':20},
{'item':2,'price':30},
{'item':3,'price':40}
(...)
]

When I'm searching orders which have at least one product larger then 20, I use this SQL:

SELECT orderId FROM example WHERE data::`0`::price > 20 OR data::`1`::price > 20 OR data::`2`::price > 20 (...) OR OR OR....

But I don't know how many products there is in orders.

Is there a solution for this problem?

Maybe something like this:

SELECT orderId FROM example WHERE data::*::price > 20 ??

Answer 1

Unfortunately we don't directly support this currently.

A workaround for this is to create a reference table with rows containing 0, 1, 2, ...:

create reference table r (i bigint primary key);
insert into r values (0), (1), (2), ...;

Then you join that and use each integer to grab the associated value from the array and test the key:

select distinct orderId from example, r where json_extract_double(json_extract_json(data, r.i), 'price') > 20;