MySQL JSON型データを部分的に抽出したい

実現したいこと

JSON型データを部分的に抽出し、複数行に分割したい

使用データ

sql
1-- テーブル追加
2/*CREATE TABLE 'json_users' ('UserId','col' JSON);*/
3
4-- レコード挿入
5/*INSERT INTO `json_users`
6VALUES
7(1,'{"name": "tanaka", "gender": 1, "options": {"x": 100, "y": 200}}'),
8(2,'{"name": "yamada", "gender": 2, "options": {"x": 300}}'),
9(3,'{"name": "suzuki", "gender": 1, "options": {"x": 100, "y": 200, "z": [1, 3, 4]}}');*/

希望データ

sql
1#     UserId   name     
2#0    1        tanaka
3#1    2        yamada
4#2    3        suzuki

試したこと

sql
1SELECT UserId, JSON_UNQUOTE(col ->"$.name") FROM json_users

→エラーが発生。抽出することができない状況です。
お詳しい方、ご教授いただきたいです。
何卒よろしくお願いいたします。

行動規範の内容に同意します

回答1件

ベストアンサー

SQL
1select UserId,trim(both '"' from col->'$."name"') as name from json_users

投稿2020/02/10 08:45

yambejp

総合スコア116724

退会済みユーザー

2020/02/10 08:50

yambejp様ご回答ありがとうございます！早速実行させていただきましたが、以下内容エラーが出てしまいます。ご教授いただけますでしょうか？ ``` Uncaught Error: near "'"'": syntax error ```

yambejp

2020/02/10 08:55

> syntax error なにかのプログラムから実行していますか？具体的にどう記載していますか？

退会済みユーザー

2020/02/10 09:04

SQLiteでそのまま実行しております。実行SQL: select UserId,trim(both '"' from col->'$."name"') as name from json_users エラー内容: Uncaught Error: near "'"'": syntax error MySQLで行なった場合下記、エラーが出ます実行SQL: select UserId,trim(both '"' from col->'$."name"') as name from json_users エラー内容: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>'$."name"') as name from json_users ' at line 1

退会済みユーザー

2020/02/10 09:05 編集

再度ご回答いただきありがとうございます。何卒よろしくお願いいたします

yambejp

2020/02/10 09:08

mysqlを前提に書いています。ホントにjson型ですよね？微妙にmysqlのバージョンが古いのかも

退会済みユーザー

2020/02/10 09:16

yamabejp様大変申し訳ございません、、 varchar(1000)でしたので、json型ではないですよね、、図々しく大変恐縮ですが、パターンマッチで抽出する方法はございますでしょうか？申し訳ございませんが、何卒よろしくお願いいたします

yambejp

2020/02/12 00:35

> varchar(1000)でしたので、json型ではない create tableでjson型を例示しているのにvarcharで指定しているというのはどうかと思いますが・・・ > パターンマッチで抽出する方法文字列型ではそれができないからJSON型が用意されているのです JSON型をうまく活用してください

行動規範の内容に同意します