Extracting first key of JSON extract in MySQL

Question

These are the columns and values of my table:

ID | RULE
123 | {"buy":{"10000":{"q":1}},"total":{"t":"DOLLAR_OFF","v":4.05}}
445 | {"buy":{"11000":{"q":1}},"total":{"t":"DOLLAR_OFF","v":2.25}}

---

My expected output:

ID | ArticleInfo
123 | 10000
445 | 11000

Pretty stuck.

Answer 1

The answer depends on the datatype you are using for the column.

If you are using the JSON data type then you can extract the key using a combination of JSON_EXTRACT and JSON_KEYS

For example if you run

SELECT ID, JSON_EXTRACT(JSON_KEYS(JSON_EXTRACT(RULE, '$.buy')),"$[0]") AS ArticleInfo FROM test;

It will return

ID | ArticleInfo
123 | "10000"
445 | "11000"

If the keys are actually always numerics you could cast them to integers by multiplying them by 1.

SELECT ID, (JSON_EXTRACT(JSON_KEYS(JSON_EXTRACT(RULE, '$.buy')),"$[0]") * 1) AS ArticleInfo FROM test;

ID | ArticleInfo
123 | 10000
445 | 11000

If it's a CHAR/VARCHAR/TEXT then you can use the same as the above but first casting the varchar to JSON using CAST(RULE AS JSON)

E.G

SELECT id, 
(JSON_EXTRACT(JSON_KEYS(JSON_EXTRACT(CAST(rule AS JSON), '$.buy')),"$[0]")* 1)
FROM test;

If you are using a version of MySQL that does not support JSON then you can use a combination of SUBSTRING, POSITION and other string functions