Search code examples
mysqlsqljsonjson-extract

Extracting first key of JSON extract in MySQL

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.

Solution

  • 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