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
– Barmar, Commented Feb 10, 2023 at 18:08
@Barmar He has offered something similar but I am unable to grasp it yet: stackoverflow.com/a/69182562/275002 — Volatil3
– Volatil3, Commented Feb 10, 2023 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
– Barmar, Commented Feb 10, 2023 at 18:12
@Barmar ah. Ok, sadly I can't give up the JSON field as it is the requirement. — Volatil3
– Volatil3, Commented Feb 10, 2023 at 18:13

Bill Karwin · Accepted Answer · 2023-02-10 19:03:07Z

1

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.

answered Feb 10, 2023 at 19:03

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

Volatil3 Over a year ago

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

Bill Karwin Over a year ago

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.

Collectives™ on Stack Overflow

MariaDB: Accessing JSON array without JSON_TABLE

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related