MariaDB: Accessing JSON array without JSON_TABLE

Question

I have a JSON field which have the following data:

[{"low": 57.07, "rsi": 0.0, "date": 1675935000000, "high": 57.07, "open": 57.07, "close": 57.07, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 0, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}, {"low": 57.0, "rsi": 0.0, "date": 1675935900000, "high": 58.49, "open": 57.07, "close": 58.4, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 2500, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}, {"low": 57.7, "rsi": 0.0, "date": 1675936800000, "high": 58.5, "open": 58.4, "close": 58.49, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 27000, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}, {"low": 58.15, "rsi": 0.0, "date": 1675937700000, "high": 59.5, "open": 58.5, "close": 59.5, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 41000, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}, {"low": 59.0, "rsi": 0.0, "date": 1675938600000, "high": 59.5, "open": 59.5, "close": 59.0, "ema_7": 0.0, "ema_21": 0.0, "symbol": "ACPL", "volume": 2500, "SUPERT_10_1_0": 0.0, "SUPERTd_10_1_0": 1, "SUPERTl_10_1_0": 0.0, "SUPERTs_10_1_0": 0.0}]

The following query perfectly works for me:

SELECT indicators_15.symbol,indicators_15.open,indicators_15.close
FROM indicators_15, 
     JSON_TABLE(data, '$[*]' COLUMNS (
                close DOUBLE  PATH '$.close',
                open DOUBLE PATH '$.open')
     ) indicators_15;

but my Hosting, Namecheap is using an older version of MariaDB hence it is failing. How can I come up with an equivalent non-JSON_TABLE version?

Below is the desired output:

You'll need to write a stored procedure to do what you want. The procedure can loop over the elements in the JSON array. — Barmar, Feb 10 '23 at 18:08
@Barmar He has offered something similar but I am unable to grasp it yet: https://stackoverflow.com/a/69182562/275002 — Volatil3, Feb 10 '23 at 18:10
That's hard-coding a set of array indexes in the subquery, and using a join to extract each index of the array. So it will only work if the array is no longer than the list of indexes. — Barmar, Feb 10 '23 at 18:12
@Barmar ah. Ok, sadly I can't give up the JSON field as it is the requirement. — Volatil3, Feb 10 '23 at 18:13
The query you show can't work on any version of MariaDB. It uses `indicators_15` for both the table and the JSON_TABLE(). Can you please create a dbfiddle to demonstrate? — Bill Karwin, Feb 10 '23 at 18:24
JSON_TABLE() was introduced in MariaDB 10.6 in July 2021 (1 year 7 months ago as we write this). I recommend asking your hosting provider to upgrade to that version or a later version, or else you should find a different hosting provider that supports a version of MariaDB that meets your requirements. — Bill Karwin, Feb 10 '23 at 18:26
@BillKarwin https://www.db-fiddle.com/f/66iWJPbKfzeT3sHT1zLFJ5/1 — Volatil3, Feb 10 '23 at 18:36
Your dbfiddle tests using MySQL 8.0. You said you were using MariaDB, which gives this error: "Not unique table/alias: 'indicators_15'" Here's a demo using MariaDB 10.6: https://dbfiddle.uk/n3H1Xnv_ Please keep in mind MariaDB and MySQL are effectively different products. MariaDB started as a fork of MySQL 5.5 in 2010, but both products have changed since then. You should not treat them as compatible anymore. — Bill Karwin, Feb 10 '23 at 18:45
@BillKarwin the site I found on Google had no MariaDB hence I opted for MYSQL. Thanks for educating me about the diff between the two. So the result I am looking for is not possible on older versions of MariaDB? — Volatil3, Feb 10 '23 at 18:49

score 1 · Accepted Answer · answered Feb 10 '23 at 19:03

To do this in an old version of MariaDB you need a table of numbers.

CREATE TABLE numbers ( number INT UNSIGNED PRIMARY KEY );
INSERT INTO numbers (number) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

Insert more numbers as needed, up to the maximum length of any of your JSON arrays.

Then you can use these numbers to extract the n-th entry from your JSON array.

SELECT i.symbol, 
  JSON_EXTRACT(i.data, CONCAT('$[', o.number, '].open')) AS open,
  JSON_EXTRACT(i.data, CONCAT('$[', o.number, '].close')) AS close
FROM indicators_15 AS i
JOIN numbers AS o ON o.number < JSON_LENGTH(i.data);

Dbfiddle using MariaDB 10.5.

Let me know if this is not clear to you and I'll try to explain further.

This really demonstrates what a bad idea it is to use JSON in a relational database. There is no reason to use JSON in your example, because every array entry has the same fields. Use JSON if you can't predict the fields. Use normal rows and columns if the fields are the same in every record. Using JSON where it is not needed — when the developer can't understand how to use it or if you are constrained to use an old version of the software that doesn't have enough support for JSON functions — will only harm your software project by increasing time to develop and therefore increasing development costs.

You're right but the problem is that I can't make architecture changes at the DB end so have to find a workaround based one existing things. The other option could be to deal at application level to display data as per the requirement — Volatil3, Feb 10 '23 at 19:11
Well I didn't mean to say that you're responsible for making a bad choice. I understand you said you have a requirement you're instructed to meet. But whoever made that choice to use JSON instead of normal rows and columns should be aware that they are responsible for making their software more difficult to develop, and probably more difficult to maintain in the future. — Bill Karwin, Feb 10 '23 at 19:13

MariaDB: Accessing JSON array without JSON_TABLE

1 Answers1