{"id":614,"date":"2020-05-06T12:33:04","date_gmt":"2020-05-06T17:33:04","guid":{"rendered":"http:\/\/thenoyes.com\/littlenoise\/?p=614"},"modified":"2020-09-23T09:17:27","modified_gmt":"2020-09-23T14:17:27","slug":"json-in-mysql-keys-which-do-not-match-a-value-the-more-elegant-way","status":"publish","type":"post","link":"https:\/\/thenoyes.com\/littlenoise\/?p=614","title":{"rendered":"JSON in MySQL: Keys which do NOT match a value (the more elegant way)"},"content":{"rendered":"\n<p>Given a JSON object:<br><code>SET @j := '{\"BOOSTER\": 1, \"RETRO\": 2, \"GUIDANCE\": 0, \"SURGEON\": 1, \"RECOVERY\": 0}';<\/code><\/p>\n\n\n\n<p>How do you find the keys which do NOT contain a value of 0?<\/p>\n\n\n\n<p>A second approach to <a href=\"https:\/\/thenoyes.com\/littlenoise\/?p=606\">finding the non-0 values from a JSON object<\/a> is to turn the keys and values into separate columns of a table. This is cleaner, but a little wordier.<\/p>\n\n\n\n<p>We&#8217;ll get the keys in one table:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT ord, keyname <\/code>\n<code>FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (<\/code>\n  <code>  ord FOR ORDINALITY,<\/code>\n   <code> keyname VARCHAR(100) PATH '$')<\/code>\n<code>) AS keyTable;\n +------+----------+\n | ord  | keyname  |\n +------+----------+\n |    1 | RETRO    |\n |    2 | BOOSTER  |\n |    3 | SURGEON  |\n |    4 | GUIDANCE |\n |    5 | RECOVERY |\n +------+----------+<\/code><\/pre>\n\n\n\n<p>And the values in a second table:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT ord, keyvalue<\/code>\n<code>FROM JSON_TABLE(@j, '$.*' COLUMNS (<\/code>\n    <code>ord FOR ORDINALITY,<\/code>\n   <code> keyvalue INT PATH '$')<\/code>\n<code>) AS valueTable;\n+------+----------+\n| ord  | keyvalue |\n+------+----------+\n|    1 | 2        |\n|    2 | 1        |\n|    3 | 1        |\n|    4 | 0        |\n|    5 | 0        |\n+------+----------+<\/code><\/pre>\n\n\n\n<p>And then join those two together:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT keyname, keyvalue\n FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (<\/code>\n    <code>ord FOR ORDINALITY,<\/code>\n   <code> keyname VARCHAR(100) PATH '$')) AS keyTable\n JOIN JSON_TABLE(@j, '$.*' COLUMNS (<\/code>\n    <code>ord FOR ORDINALITY,<\/code>\n   <code> keyvalue INT PATH '$')<\/code>\n<code>) AS valueTable USING (ord);\n +----------+----------+\n | keyname  | keyvalue |\n +----------+----------+\n | RETRO    | 2        |\n | BOOSTER  | 1        |\n | SURGEON  | 1        |\n | GUIDANCE | 0        |\n | RECOVERY | 0        |\n +----------+----------+<\/code><\/pre>\n\n\n\n<p>Now you can use ordinary operations like a WHERE clause to do your filtering, and then JSON_ARRAYAGG() to put them all back into JSON at the end.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT JSON_ARRAYAGG(keyname) AS j\n FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (<\/code>\n    <code>ord FOR ORDINALITY,<\/code>\n   <code> keyname VARCHAR(100) PATH '$')) AS keyTable\n JOIN JSON_TABLE(@j, '$.*' COLUMNS (<\/code>\n    <code>ord FOR ORDINALITY,<\/code>\n   <code> keyvalue INT PATH '$')<\/code>\n<code>) AS valueTable USING (ord)\n WHERE keyvalue != 0;\n +---------------------------------+\n | j                               |\n +---------------------------------+\n | [\"RETRO\", \"BOOSTER\", \"SURGEON\"] |\n +---------------------------------+<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Given a JSON object:SET @j := &#8216;{&#8220;BOOSTER&#8221;: 1, &#8220;RETRO&#8221;: 2, &#8220;GUIDANCE&#8221;: 0, &#8220;SURGEON&#8221;: 1, &#8220;RECOVERY&#8221;: 0}&#8217;; How do you find the keys which do NOT contain a value of 0? A second approach to finding the non-0 values from a JSON object is to turn the keys and values into separate columns of a table. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[4],"tags":[],"class_list":["post-614","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-9U","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=614"}],"version-history":[{"count":10,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/614\/revisions"}],"predecessor-version":[{"id":638,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/614\/revisions\/638"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}