CODEWITHSUNDEEP
jsonmariadb
Volatil3
Volatil3

Reputation: 14978

MariaDB: Accessing JSON array without JSON_TABLE

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:

enter image description here

Upvotes: 1

Views: 342

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

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.

Upvotes: 1

Related Questions