mysql: extract multiline vertical JSON output with perl and jq

mysql-vertical-result-EXPLAIN-extract-json.sh

#!/usr/bin/env bash
# query will onlny be explained, not executed
mysql --defaults-extra-file=$HOME/.mysql-credentials.cnf 2>/dev/null -Dworld -E -t<<EOF | perl -000 -nlE  '/(?<=EXPLAIN:)(.*)/ms && say $1' | jq 
EXPLAIN  FORMAT=JSON SELECT * FROM city WHERE name = 'London';
EOF

# /* expected result:

# {
#   "query_block": {
#     "select_id": 1,
#     "cost_info": {
#       "query_cost": "410.85"
#     },
#     "table": {
#       "table_name": "city",
#       "access_type": "ALL",      <=================== !!!
#       "rows_examined_per_scan": 4046,
#       "rows_produced_per_join": 404,
#       "filtered": "10.00",
#       "cost_info": {
#         "read_cost": "370.39",
#         "eval_cost": "40.46",
#         "prefix_cost": "410.85",
#         "data_read_per_join": "97K"
#       },
#       "used_columns": [
#         "ID",
#         "Name",
#         "CountryCode",
#         "District",
#         "Population"
#       ],
#       "attached_condition": "(`world`.`city`.`Name` = 'London')"
#     }
#   }
# }

# */

# traditional format
mysql --defaults-extra-file=$HOME/.mysql-credentials.cnf  -Dworld -E -t<<EOF | grep -v NULL  
EXPLAIN   SELECT * FROM city WHERE name = 'London';
EOF

# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: city
#          type: ALL
#          rows: 4046
#      filtered: 10.00
#         Extra: Using where

# explain analyze
mysql --defaults-extra-file=$HOME/.mysql-credentials.cnf 2>/dev/null -Dworld -E -t<<EOF | perl -000 -nlE  '/(?<=EXPLAIN:)(.*)/ms && say $1' | jq 
EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM city WHERE name = 'London';
EOF

## read from bottom to top
# *************************** 1. row ***************************
# EXPLAIN: -> Filter: (city.`Name` = 'London')  (cost=410.85 rows=405) (actual time=0.211..1.469 rows=2 loops=1)
#     -> Table scan on city  (cost=410.85 rows=4046) (actual time=0.045..1.080 rows=4079 loops=1)

# cost: mysql internal units
# actual time: milliseconds

# EXPLAIN ANALYZE output is organized into a series of nodes. 
# At the lowest level, there are nodes that scan tables or search indexes. 
# Higher-level nodes take the results, from the lower level ones, 
# and operate on them. 
# Here, it can be easier to understand the execution by reading the output 
# from the inside and out..