Extract JSON object's values as array from MySQL JSON column

Question

I have a MySQL column of json type. In there, a dict like JSON object is stored. Now, I want to extract the values from this JSON object and create a JSON array.

How can I achieve this?

Example Query

with json_objs(json_col) as (
  select CAST('{"key1": "value1", "key2": "value2"}' AS JSON)
  UNION ALL
  select CAST('{"key3": "value3", "key4": "value4"}' AS JSON)
)
select SOME_EXPR_I_CAN_T_FIGURE_OUT from json_objs

Expected result

+----------------------+
| resulting_column     |
+----------------------+
| ["value1", "value2"] |
| ["value3", "value4"] |
+----------------------+

(If table DDL is desired:)

CREATE TABLE `json_objs` (
  `json_col` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Does mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions help? — Nico Haase
– Nico Haase, Commented Nov 28, 2018 at 9:39

marekful · Accepted Answer · 2018-11-28 10:03:03Z

10

You can use the -> operator as in the expression column -> path as follows:

create table table1 (
   json_dict JSON
);

insert into table1 values('{"ak":"av","bk":"bv"}');
insert into table1 values('{"ak2":"av2","bk2":"bv2"}');

select * from table1;
+------------------------------+
| json_dict                    |
+------------------------------+
| {"ak": "av", "bk": "bv"}     |
| {"ak2": "av2", "bk2": "bv2"} |
+------------------------------+
2 rows in set (0.00 sec)

select json_dict->"$.*" from table1;
+------------------+
| json_dict->"$.*" |
+------------------+
| ["av", "bv"]     |
| ["av2", "bv2"]   |
+------------------+
2 rows in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-column-path

answered Nov 28, 2018 at 10:03

marekful

15.4k6 gold badges39 silver badges63 bronze badges

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

1 Comment

Syed Over a year ago

saved a run around

Madhur Bhaiya · Accepted Answer · 2018-11-28 10:07:37Z

5

Other than -> operator, you can also use the Json_Extract() function:

Schema (MySQL v8.0)

create table table1 (
   json_dict JSON
);

insert into table1 values('{"ak":"av","bk":"bv"}');
insert into table1 values('{"ak2":"av2","bk2":"bv2"}');

Query #1

select JSON_EXTRACT(json_dict, '$.*') from table1;

| JSON_EXTRACT(json_dict, '$.*') |
| ------------------------------ |
| ["av", "bv"]                   |
| ["av2", "bv2"]                 |

View on DB Fiddle

answered Nov 28, 2018 at 10:07

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

2 Comments

marekful Over a year ago

I was faster and you copied code from my answer. However, I upvoted yours because you showed a slightly different alternative :)

Madhur Bhaiya Over a year ago

@marekful Thanks for the fiddle. I was lazy to create a fiddle; so pouched on your fiddle :P

Collectives™ on Stack Overflow

Extract JSON object's values as array from MySQL JSON column

Example Query

Expected result

(If table DDL is desired:)

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Example Query

Expected result

(If table DDL is desired:)

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related