{"id":26260,"date":"2024-03-16T06:25:40","date_gmt":"2024-03-16T06:25:40","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/"},"modified":"2024-03-22T08:09:15","modified_gmt":"2024-03-22T08:09:15","slug":"how-can-i-query-fields-within-a-json-object-in-mysql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/","title":{"rendered":"How can I query fields within a JSON object in MySQL?"},"content":{"rendered":"<p>In MySQL, you can retrieve data from a JSON field using the JSON_EXTRACT function. This function takes two parameters: the first parameter is the JSON field you want to query, and the second parameter is the key path you want to extract.<\/p>\n<p>For example, let&#8217;s say there is a JSON field named &#8216;data&#8217; that contains the following information:<\/p>\n<pre class=\"post-pre\"><code>{\r\n   \"name\": \"John\",\r\n   \"age\": 30,\r\n   \"address\": {\r\n      \"city\": \"New York\",\r\n      \"state\": \"NY\"\r\n   }\r\n}\r\n<\/code><\/pre>\n<p>To retrieve the value of the &#8216;name&#8217; key in the data field, you can use the following query statement:<\/p>\n<pre class=\"post-pre\"><code>SELECT JSON_EXTRACT(data, '$.name') AS name FROM table_name;\r\n<\/code><\/pre>\n<p>The result will return the value of the key &#8220;name&#8221;.<\/p>\n<p>If you want to retrieve the value of a key in a nested JSON field, you can use dot notation to indicate the nested levels. For example, to retrieve the value of the city key under the address key in the data field, you can use the following query statement:<\/p>\n<pre class=\"post-pre\"><code>SELECT JSON_EXTRACT(data, '$.address.city') AS city FROM table_name;\r\n<\/code><\/pre>\n<p>The result will return the value of the key &#8220;city&#8221;.<\/p>\n<p>If you want to retrieve values from a JSON array, you can use an index number. For example, if the data field contains a JSON array called hobbies:<\/p>\n<pre class=\"post-pre\"><code>{\r\n   \"name\": \"John\",\r\n   \"age\": 30,\r\n   \"hobbies\": [\"swimming\", \"reading\", \"traveling\"]\r\n}\r\n<\/code><\/pre>\n<p>You can use the following query statement to retrieve the first value in the hobbies array:<\/p>\n<pre class=\"post-pre\"><code>SELECT JSON_EXTRACT(data, '$.hobbies[0]') AS first_hobby FROM table_name;\r\n<\/code><\/pre>\n<p>The result will return the first value in the hobbies array.<\/p>\n<p>Please note that the &#8220;table_name&#8221; in the above query should be replaced with the actual name of the table.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In MySQL, you can retrieve data from a JSON field using the JSON_EXTRACT function. This function takes two parameters: the first parameter is the JSON field you want to query, and the second parameter is the key path you want to extract. For example, let&#8217;s say there is a JSON field named &#8216;data&#8217; that contains [&hellip;]<\/p>\n","protected":false},"author":9,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-26260","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How can I query fields within a JSON object in MySQL? - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can I query fields within a JSON object in MySQL?\" \/>\n<meta property=\"og:description\" content=\"In MySQL, you can retrieve data from a JSON field using the JSON_EXTRACT function. This function takes two parameters: the first parameter is the JSON field you want to query, and the second parameter is the key path you want to extract. For example, let&#8217;s say there is a JSON field named &#8216;data&#8217; that contains [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-16T06:25:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-22T08:09:15+00:00\" \/>\n<meta name=\"author\" content=\"Ava Mitchell\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ava Mitchell\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/\"},\"author\":{\"name\":\"Ava Mitchell\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64\"},\"headline\":\"How can I query fields within a JSON object in MySQL?\",\"datePublished\":\"2024-03-16T06:25:40+00:00\",\"dateModified\":\"2024-03-22T08:09:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/\"},\"wordCount\":235,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/\",\"name\":\"How can I query fields within a JSON object in MySQL? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-16T06:25:40+00:00\",\"dateModified\":\"2024-03-22T08:09:15+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can I query fields within a JSON object in MySQL?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64\",\"name\":\"Ava Mitchell\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g\",\"caption\":\"Ava Mitchell\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/avamitchell\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How can I query fields within a JSON object in MySQL? - Blog - Silicon Cloud","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"How can I query fields within a JSON object in MySQL?","og_description":"In MySQL, you can retrieve data from a JSON field using the JSON_EXTRACT function. This function takes two parameters: the first parameter is the JSON field you want to query, and the second parameter is the key path you want to extract. For example, let&#8217;s say there is a JSON field named &#8216;data&#8217; that contains [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-16T06:25:40+00:00","article_modified_time":"2024-03-22T08:09:15+00:00","author":"Ava Mitchell","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Ava Mitchell","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/"},"author":{"name":"Ava Mitchell","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64"},"headline":"How can I query fields within a JSON object in MySQL?","datePublished":"2024-03-16T06:25:40+00:00","dateModified":"2024-03-22T08:09:15+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/"},"wordCount":235,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/","url":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/","name":"How can I query fields within a JSON object in MySQL? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-16T06:25:40+00:00","dateModified":"2024-03-22T08:09:15+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-can-i-query-fields-within-a-json-object-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How can I query fields within a JSON object in MySQL?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64","name":"Ava Mitchell","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g","caption":"Ava Mitchell"},"url":"https:\/\/www.silicloud.com\/blog\/author\/avamitchell\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/26260","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=26260"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/26260\/revisions"}],"predecessor-version":[{"id":60413,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/26260\/revisions\/60413"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=26260"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=26260"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=26260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}