CODEWITHSUNDEEP
mysqlparsing
Pythoner
Pythoner

Reputation: 5585

How to parse a map field in mysql?

I have a map based field like below, I am wondering how to parse it in mysql? For example, how to count how many times, xxx1, has appeared?

["1111:xxx1","2222:xxx2","3333:xxx3"]

Upvotes: 1

Views: 596

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562330

SQL is not a great language for parsing text. You should consider storing data in a relational way, then you can use traditional SQL queries like:

SELECT COUNT(*) FROM mytable WHERE column2 = 'xxx1';

The data you showed looks like a valid JSON array, but each string in the array is further split into "key:value" format. I wonder why you did not format it fully as a JSON object instead of an array: {"1111": "xxx1","2222": "xxx2","3333": "xxx3"}.

But with the data as given, one could do the following in MySQL 8.0 (this solution relies on JSON_TABLE() which is not supported in older versions of MySQL).

set @j = '["1111:xxx1","2222:xxx2","3333:xxx3"]';

select * from json_table(@j, '$[*]' columns(element varchar(20) path '$')) as j;
+-----------+
| element   |
+-----------+
| 1111:xxx1 |
| 2222:xxx2 |
| 3333:xxx3 |
+-----------+

This splits the array into rows. Now we can split that on ':':

select 
  substring_index(element, ':', 1) as col1,
  substring_index(element, ':', -1) as col2 
from json_table(@j, '$[*]' columns(element varchar(20) path '$')) as j;
+------+------+
| col1 | col2 |
+------+------+
| 1111 | xxx1 |
| 2222 | xxx2 |
| 3333 | xxx3 |
+------+------+

And finally use that as a derived-table subquery so we can reference the column aliases, search for the value you want, and count the occurrences.

select count(*) from (
  select 
    substring_index(element, ':', 1) as col1, 
    substring_index(element, ':', -1) as col2 
  from json_table(@j, '$[*]' columns(element varchar(20) path '$')) as j
) as t
WHERE t.col2 = 'xxx1';
+----------+
| count(*) |
+----------+
|        1 |
+----------+

This is a lot of inconvenience compared to the query I showed at the start. This is why storing data in normal rows and columns is better than cramming everything into JSON-like semi-structured documents.

Upvotes: 1

Related Questions