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.

What is the column type of the RULE column? Is it a varchar or a JSON Field — Steve Mapes, Mar 03 '20 at 14:10
In that case take a look at my answer below and you want to use the last part which supports converting TEXT to JSON and then parsing it — Steve Mapes, Mar 03 '20 at 14:33
What have you tried so far? Where **exactly** are you stuck? — Nico Haase, Mar 04 '20 at 16:05

Steve Mapes · Accepted Answer · 2021-01-12T11:10:37.303

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

1 Answers1