Cara menggunakan extract function mysql

Questions : How to select from MySQL JSON array as rows

2022-09-18T22:26:11+00:00 2022-09-18T22:26:11+00:00

Table of Contents

• Questions : How to select from MySQL JSON array as rows
• Answers 1 : of How to select from MySQL JSON array as rows
• How do I query a JSON column in MySQL?
• Is JSON an array MySQL?
• What is Json_table?
• What is JSON extract?

Table of Contents

• Questions : How to select from MySQL JSON array as rows
• Answers 1 : of How to select from MySQL JSON array as rows
• How do I query a JSON column in MySQL?
• Is JSON an array MySQL?
• What is Json_table?
• What is JSON extract?

807

Using MySQL 5.7, how can I select array anycodings_sql elements from a JSON column, as rows?

Table Person

id      data
1       [{"name":"one"},{"name":"two"}]
2       [{"name":"three"},{"name":"four"}]

I want to "pivot" the elements in the JSON anycodings_sql array to rows. Non-working SQL below...

SELECT
    p.id AS personId,
    d->'$.name' AS name
FROM
    Person p
    JSON_EXTRACT(p.data) d  # <-- not valid SQL
WHERE
    d->'$.name' <> 'three'

Expected Output

personId  name
1         one
1         two
2         four

Total Answers 1

24

Answers 1 : of How to select from MySQL JSON array as rows

If you are running MySQL 8.0, you can anycodings_arrays use json_table():

select p.id, x.name
from person p
cross join json_table(
    p.data,
    '$[*]' columns (name varchar(50) path '$.name')
) x
where x.name <> 'three'

In earlier versions, one alternative is anycodings_arrays to use a derived table of numbers to anycodings_arrays unnest the array:

select *
from (
    select p.id, json_unquote(json_extract(p.data, concat('$[', n.num, '].name'))) name
    from person p
    inner join (select 0 num union all select 1 union all select 2) n
        on n.num < json_length(p.data)
) t
where name <> 'three'

The union all subquery should contain at anycodings_arrays least as many elements as the maximum anycodings_arrays number of elements in any JSON array of anycodings_arrays your table.

Demos on DB Fiddle:

• MySQL 8.0

• MySQL 5.7

0

2022-09-18T22:26:11+00:00 2022-09-18T22:26:11+00:00Answer Link

mRahman

For MySQL 8+, see this answer.

For older versions, this is how I do it:

1. Create a new table pseudo_rows with values from 0 until 99 - these will be used as keys (if your array has more than a hundred values, add more values into pseudo_rows).

NOTE: If you're running MariaDB, you can anycodings_json skip this and simply use pseudo sequence anycodings_json tables (e.g. seq_0_to_99).

CREATE TABLE `pseudo_rows` (
  `row` int(10) unsigned NOT NULL,
  PRIMARY KEY (`row`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT pseudo_rows VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92), (93), (94), (95), (96), (97), (98), (99)

1. For this example, I'll be using a table events which stores groups of artists:

CREATE TABLE `events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `artists` json DEFAULT NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `events` (`id`, `artists`) VALUES ('1', '[{\"id\": 123, \"name\": \"Pink Floyd\"}]');
INSERT INTO `events` (`id`, `artists`) VALUES ('2', '[{\"id\": 456, \"name\": \"Nirvana\"}, {\"id\": 789, \"name\": \"Eminem\"}]');

The query to get all artists, one per anycodings_json row, is as follows:

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(events.artists, CONCAT('$[', pseudo_rows.row, '].name'))) AS performer
FROM events
JOIN pseudo_rows
HAVING performer IS NOT NULL

And the resultset is:

performer
---------
Pink Floyd
Nirvana
Eminem

UPDATE: This is now possible in MySQL 8 via the JSON_TABLE function: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

I'm loving the new JSON functions in MySQL 5.7, but running into a block trying to merge values from JSON into a normal table structure.

Grabbing JSON, manipulating and extracting arrays from it etc. is simple. JSON_EXTRACT all the way. But what about the inverse, going from a JSON array to rows? Perhaps I am dense on the existing MySQL JSON functionality, but I haven't been able to figure that one out.

For example, say I have a JSON array and want to insert a row for each element in the array with its value? The only way I have found is to write a bunch of JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') etc and union them together.

Or, say I have a JSON array and want to GROUP_CONCAT() it to a single comma separated string?

In other words, I know I can do this:

SET @j = '[1, 2, 3]';
 SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val
   FROM   
   (    
     SELECT 0 AS n    
     UNION    
     SELECT 1 AS n    
     UNION    
     SELECT 2 AS n    
     UNION    
     SELECT 3 AS n    
     UNION    
     SELECT 4 AS n    
     UNION    
     SELECT 5 AS n    
   ) x
 WHERE x.n < JSON_LENGTH(@j);
 

But that hurts my eyes. And my heart.

How can I do something like:

SET @j = '[1, 2, 3]';
 SELECT GROUP_CONCAT(JSON_EXTRACT(@j, '$[ * ]'))
 

... and have it concatenate together the values in the array vs. the JSON array itself?

I guess what I'm looking for here is some sort of JSON_SPLIT along the lines of:

SET @j = '[1, 2, 3]';
 SELECT GROUP_CONCAT(val)
 FROM
   JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')
 

If MySQL had a proper STRING_SPLIT(val, 'separator') table returning function, I could hack it (escaping be damned), but that's not available either.

How do I query a JSON column in MySQL?

MySQL provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes. As you can see ->> returns output as quoted strings, while -> returns values as they are. You can also use these operators in WHERE clause as shown below.

Is JSON an array MySQL?

In MySQL, you can use the JSON_ARRAY() function to create a JSON array from a list of values. You provide each value as a separate argument. Each argument becomes a separate element of the array. The function also accepts an empty list (i.e. you provide no arguments).

What is Json_table?

JSON_TABLE creates a relational view of JSON data. It maps the result of a JSON data evaluation into relational rows and columns. You can query the result returned by the function as a virtual relational table using SQL.

What is JSON extract?

JSON_EXTRACT. Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. JSON-formatted STRING or JSON. JSON_EXTRACT_SCALAR. Extracts a scalar value.