TL;DR
jqの記法が覚えられないのでperl -MJSONで代用しています、というだけの記事です。
このエントリは Perl Advent Calendar 2021 の14日目の記事です。
サンプルJSONはテキトーに作ったやつ。
やりたくなるのはだいたいこんなやつ。
$ cat generated.json | jq '.[0].tags' ### これが全要素ぶん欲しくなる
[
"occaecat",
"incididunt",
"dolore",
"reprehenderit",
"officia",
"cupidatat",
"qui"
]
$ cat generated.json | jq '.[*].tags' ### 違う
jq: error: syntax error, unexpected '*' (Unix shell quoting issues?) at <top-level>, line 1:
.[*].tags
jq: 1 compile error
$ cat generated.json | jq '.*.tags' ### 違う
jq: error (at <stdin>:226): Cannot index array with string "tags"
jqは毎回ググらないといけないけど、perlなら手癖でサクっと書けて便利だね！ というだけの話でした。
$ cat generated.json | perl -MJSON -E 'my $json= from_json(join("", <>)); foreach (@$json) { say join(", ", @{$_->{tags}}) }'
occaecat, incididunt, dolore, reprehenderit, officia, cupidatat, qui
veniam, minim, amet, anim, esse, consectetur, nisi
dolor, ipsum, dolor, id, voluptate, esse, minim
proident, sit, veniam, occaecat, mollit, do, reprehenderit
aliquip, mollit, magna, laboris, non, mollit, reprehenderit
で、手癖でサクっと書けるといえばMySQLな訳(?)で、MySQL 8.0には JSON_TABLE関数 があるので、ワンチャンワンライナーで書けるんじゃないかと。
$ mysql80 -e "SELECT * FROM JSON_TABLE('"$(cat generated.json)"', '\$[*]' COLUMNS(id TEXT PATH '$._id', tags JSON PATH '$.tags')) AS j;
..
エスケープが面倒くさくて挫折しました。
コマンドラインクライアント開いちゃえば(慣れるまでやれば)何とかなりそうな気はするんですが、おとなしくPerlを使いたいと思います。
mysql80 23> SET @j := '[ { "_id": "61b8175f0e474cbda9cfe8ce", "index": 0, "guid": "a50ab0a0-7053-4473-b211-062232793c5c", "isActive": true, "balance": "$1,446.15", "picture": "http://placehold.it/32x32", "age": 26, "eyeColor": "brown", "name": "Galloway Mueller", "gender": "male", "company": "LUDAK", "email": "gallowaymueller@ludak.com", "phone": "+1 (953) 468-2935", "address": "587 Bank Street, Savannah, Delaware, 3971", "about": "Lorem irure reprehenderit pariatur et voluptate. Consectetur aute deserunt ex deserunt pariatur minim sit fugiat elit minim. Ullamco excepteur magna aliquip do ad aliqua.\\r\\n", "registered": "2017-09-08T10:09:10 -09:00", "latitude": 36.049314, "longitude": 94.927171, "tags": [ "occaecat", "incididunt", "dolore", "reprehenderit", "officia", "cupidatat", "qui" ], "friends": [ { "id": 0, "name": "Abigail Odonnell" }, { "id": 1, "name": "Shelton Rosales" }, { "id": 2, "name": "Marisa Robertson" } ], "greeting": "Hello, Galloway Mueller! You have 2 unread messages.", "favoriteFruit": "banana" }, { "_id": "61b8175f8eea11b2daa74ccd", "index": 1, "guid": "2db0dfc9-6e52-4d27-88ad-736e31637b11", "isActive": false, "balance": "$2,183.95", "picture": "http://placehold.it/32x32", "age": 35, "eyeColor": "green", "name": "Herrera Leblanc", "gender": "male", "company": "ZOLAR", "email": "herreraleblanc@zolar.com", "phone": "+1 (910) 438-3490", "address": "774 Woods Place, Dodge, Indiana, 4704", "about": "Tempor dolor magna ex cupidatat ad Lorem. Non ullamco excepteur ullamco do qui sit tempor adipisicing velit aliquip ea ea cillum. Quis duis non ea dolor. Labore aute do Lorem do pariatur cillum excepteur eu.\\r\\n", "registered": "2014-03-21T02:23:45 -09:00", "latitude": 18.114458, "longitude": -150.106866, "tags": [ "veniam", "minim", "amet", "anim", "esse", "consectetur", "nisi" ], "friends": [ { "id": 0, "name": "Tami Vinson" }, { "id": 1, "name": "Greta Aguilar" }, { "id": 2, "name": "Tabitha Kramer" } ], "greeting": "Hello, Herrera Leblanc! You have 7 unread messages.", "favoriteFruit": "strawberry" }, { "_id": "61b8175f50ab99cf353821e4", "index": 2, "guid": "58a3b7b3-f690-4bd8-9e45-b25fc2820bfc", "isActive": false, "balance": "$2,769.89", "picture": "http://placehold.it/32x32", "age": 40, "eyeColor": "brown", "name": "Leonard Morin", "gender": "male", "company": "QUOTEZART", "email": "leonardmorin@quotezart.com", "phone": "+1 (991) 497-3228", "address": "867 Polar Street, Thomasville, Idaho, 4492", "about": "Duis consequat exercitation sint non in culpa adipisicing aute ut adipisicing sunt labore consectetur nisi. Adipisicing nostrud enim elit magna amet excepteur veniam consequat nisi minim. Velit laborum aute aute enim duis veniam.\\r\\n", "registered": "2014-03-08T04:09:22 -09:00", "latitude": -75.02935, "longitude": 143.205584, "tags": [ "dolor", "ipsum", "dolor", "id", "voluptate", "esse", "minim" ], "friends": [ { "id": 0, "name": "Browning Pena" }, { "id": 1, "name": "Shelby Oconnor" }, { "id": 2, "name": "Stark Peck" } ], "greeting": "Hello, Leonard Morin! You have 1 unread messages.", "favoriteFruit": "banana" }, { "_id": "61b8175fb7c793f4cce77035", "index": 3, "guid": "ebb5a062-9f91-44a3-a035-d3705a408bc4", "isActive": false, "balance": "$3,895.62", "picture": "http://placehold.it/32x32", "age": 25, "eyeColor": "green", "name": "Inez Prince", "gender": "female", "company": "RUGSTARS", "email": "inezprince@rugstars.com", "phone": "+1 (909) 410-3299", "address": "580 Preston Court, Coyote, District Of Columbia, 6172", "about": "Sunt adipisicing est excepteur exercitation ea. Aliqua et eu aliqua officia sunt. Aute dolore elit elit adipisicing mollit non. Officia ea commodo tempor eiusmod. Do sint officia eu nostrud ex. Duis excepteur eiusmod amet excepteur ad. Incididunt deserunt ut velit officia aliquip ullamco elit.\\r\\n", "registered": "2021-06-06T03:53:17 -09:00", "latitude": 53.834649, "longitude": 46.190966, "tags": [ "proident", "sit", "veniam", "occaecat", "mollit", "do", "reprehenderit" ], "friends": [ { "id": 0, "name": "Forbes Chavez" }, { "id": 1, "name": "Marietta Edwards" }, { "id": 2, "name": "Leann Williams" } ], "greeting": "Hello, Inez Prince! You have 5 unread messages.", "favoriteFruit": "strawberry" }, { "_id": "61b8175fc39c6d3d5f37a882", "index": 4, "guid": "cddcaa87-8807-4601-95d7-85c10bcdcf19", "isActive": false, "balance": "$2,724.38", "picture": "http://placehold.it/32x32", "age": 32, "eyeColor": "blue", "name": "Cannon Bullock", "gender": "male", "company": "EMTRAK", "email": "cannonbullock@emtrak.com", "phone": "+1 (876) 596-2034", "address": "820 Vandam Street, Naomi, Arizona, 1294", "about": "Irure laboris et labore irure Lorem consequat consequat nostrud ad incididunt et Lorem. Incididunt id ipsum mollit do proident. Ipsum exercitation eiusmod et proident occaecat qui est tempor quis aliquip fugiat consectetur. Excepteur exercitation laborum duis occaecat qui ad ut in laboris in elit officia. Magna consectetur proident deserunt minim. Exercitation labore ullamco cupidatat officia cupidatat. Laboris id elit aliquip et laboris nostrud ullamco.\\r\\n", "registered": "2016-05-13T04:25:45 -09:00", "latitude": -50.009625, "longitude": -38.790491, "tags": [ "aliquip", "mollit", "magna", "laboris", "non", "mollit", "reprehenderit" ], "friends": [ { "id": 0, "name": "Bradshaw Barr" }, { "id": 1, "name": "Meredith Donovan" }, { "id": 2, "name": "Lester Carter" } ], "greeting": "Hello, Cannon Bullock! You have 8 unread messages.", "favoriteFruit": "apple" } ]'; -- \r\nを\\r\\nにエスケープする必要がある…
Query OK, 0 rows affected (0.00 sec)
mysql80 23> SELECT * FROM JSON_TABLE(@j, '$[*]' COLUMNS(id TEXT PATH '$._id', tags JSON PATH '$.tags')) AS j;
+--------------------------+--------------------------------------------------------------------------------------+
| id | tags |
+--------------------------+--------------------------------------------------------------------------------------+
| 61b8175f0e474cbda9cfe8ce | ["occaecat", "incididunt", "dolore", "reprehenderit", "officia", "cupidatat", "qui"] |
| 61b8175f8eea11b2daa74ccd | ["veniam", "minim", "amet", "anim", "esse", "consectetur", "nisi"] |
| 61b8175f50ab99cf353821e4 | ["dolor", "ipsum", "dolor", "id", "voluptate", "esse", "minim"] |
| 61b8175fb7c793f4cce77035 | ["proident", "sit", "veniam", "occaecat", "mollit", "do", "reprehenderit"] |
| 61b8175fc39c6d3d5f37a882 | ["aliquip", "mollit", "magna", "laboris", "non", "mollit", "reprehenderit"] |
+--------------------------+--------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
明日はまだ決まっていません！
