Machine-readable output from “wp db query”

I use WP-CLI constantly, and the ability to --format many of its commands to output results as JSON (or a CSV, or a nice table printed in your terminal) is a powerful tool for routing WP-CLI output into other commands. The wp db query command doesn’t support --format, though—it outputs the results from MySQL directly, and that output is designed for eyes, not machines. Even if all you want is a list of IDs, you can’t use this output in another application without piping it through a variety of cleaning steps:

wp db query 'select ID from wp_posts where post_status like "draft" limit 10'

+-------+
| ID    |
+-------+
|  9830 |
| 10169 |
| 13612 |
| 13616 |
| 13617 |
| 13620 |
| 13621 |
| 13622 |
| 13623 |
| 13624 |
+-------+

I was frustrated by this again yesterday, and realized I’d missed a key comment in the wp db query documentation page:

# Pass extra arguments through to MySQL
$ wp db query 'SELECT * FROM wp_options WHERE option_name="home"' --skip-column-names
+---+------+------------------------------+-----+
| 2 | home | http://wordpress-develop.dev | yes |
+---+------+------------------------------+-----+

“Pass extra arguments through to MySQL,” you say? That’s exactly what we want! With --skip-column-names and --silent (to remove the | | table-formatting) we now receive a list of IDs on newlines, which can easily be used as input for other commands:

wp db query 'select ID from wp_posts where post_status like "draft" limit 10' --skip-column-names --silent

9830
10169
13612
13616
13617
13620
13621
13622
13623
13624

If we wanted to bulk-publish these draft posts, for example, we can pass this ID list to wp post update:

wp post update $( wp db query 'select ID from wp_posts where post_status like "publish" limit 10' --skip-column-names --silent ) --post_status=publish

The nested commands can get unwieldy, but knowing how to get query output without the table formatting has already significantly sped up some tricky bulk tasks. It’s been a major time saver for me, and I hope it is useful to you as well.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.