How to Use JSON Data Fields in MySQL Databases

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

叛逆的沙发 · Convert data ...· 1 月前 ·

飞翔的打火机 · imageStore(3g) — ...· 1 月前 ·

苦恼的伤痕 · imageStore - GLSL 4 - ...· 1 月前 ·

阳刚的太阳 · openlayer开发报错记录：Uncaug ...· 1 月前 ·

豪爽的热水瓶 · requests--传递参数 - ...· 2 周前 ·

才高八斗的柳树 · 类似番茄todo的软件-pc软件园· 2 月前 ·

有情有义的山羊 · 3 ...· 2 月前 ·

酒量小的紫菜汤 · ClickHouse vs. ...· 3 月前 ·

讲道义的楼房 · 網路門市 - 台灣大哥大｜Open ...· 4 月前 ·

文质彬彬的红豆 · 乌法鲁奥德赛手游最新安卓版下载-乌法鲁奥德赛 ...· 5 月前 ·

</noscript><div id="___gatsby"><div style="outline:none" tabindex="-1" id="gatsby-focus-wrapper"><div data-display="true" data-mobile="false" class="s13se1v"/><header class="relative z-40 w-full"><nav class="header-module--nav--ec8cc relative h-10 bg-spBlack" aria-label="Main navigation"><div class="container relative flex items-center justify-between h-full mx-auto max-w-screen-xl"><a href="/" aria-label="SitePoint" class="flex items-center px-gs-1"><svg viewbox="0 0 123 27" class="header-module--logo--07b7e fill-current text-white"><title>SitePoint</title><g fill-rule="evenodd"><path d="M31.36,16.75 C31.56,17.07 31.82,17.31 32.14,17.50 C32.46,17.68 32.82,17.82 33.23,17.89 C33.63,17.97 34.05,18.01 34.48,18.01 C34.81,18.01 35.16,17.99 35.52,17.94 C35.89,17.89 36.22,17.81 36.53,17.68 C36.83,17.55 37.08,17.36 37.28,17.11 C37.48,16.87 37.58,16.55 37.58,16.17 C37.58,15.64 37.37,15.24 36.96,14.97 C36.55,14.69 36.03,14.47 35.41,14.31 C34.79,14.14 34.12,13.99 33.39,13.85 C32.66,13.71 31.99,13.52 31.37,13.27 C30.75,13.03 30.23,12.68 29.82,12.23 C29.41,11.78 29.20,11.16 29.20,10.36 C29.20,9.73 29.34,9.20 29.63,8.75 C29.92,8.30 30.30,7.94 30.75,7.66 C31.20,7.38 31.72,7.17 32.29,7.03 C32.86,6.90 33.42,6.83 33.99,6.83 C34.71,6.83 35.38,6.89 35.99,7.01 C36.61,7.13 37.15,7.34 37.62,7.63 C38.09,7.93 38.46,8.33 38.75,8.83 C39.03,9.34 39.19,9.96 39.24,10.72 L37.13,10.72 C37.10,10.32 36.99,9.98 36.81,9.72 C36.63,9.46 36.40,9.25 36.12,9.08 C35.84,8.92 35.53,8.81 35.19,8.74 C34.85,8.66 34.51,8.63 34.16,8.63 C33.84,8.63 33.53,8.65 33.20,8.70 C32.88,8.75 32.59,8.83 32.32,8.95 C32.06,9.07 31.84,9.23 31.68,9.43 C31.51,9.63 31.43,9.89 31.43,10.21 C31.43,10.56 31.56,10.86 31.82,11.09 C32.07,11.32 32.40,11.51 32.80,11.66 C33.19,11.82 33.64,11.95 34.13,12.05 C34.63,12.15 35.13,12.26 35.62,12.37 C36.15,12.49 36.67,12.62 37.17,12.78 C37.67,12.94 38.12,13.15 38.51,13.42 C38.90,13.68 39.21,14.01 39.45,14.41 C39.69,14.81 39.81,15.31 39.81,15.90 C39.81,16.66 39.65,17.28 39.33,17.77 C39.01,18.27 38.58,18.67 38.06,18.98 C37.54,19.28 36.96,19.49 36.30,19.61 C35.65,19.73 35.00,19.79 34.36,19.79 C33.65,19.79 32.97,19.72 32.32,19.58 C31.68,19.43 31.11,19.20 30.61,18.87 C30.12,18.54 29.72,18.11 29.42,17.57 C29.13,17.03 28.96,16.38 28.93,15.61 L31.03,15.61 C31.05,16.06 31.16,16.44 31.36,16.75" id="Fill-1"/><path d="M42.27,7.12 L44.37,7.12 L44.37,19.53 L42.27,19.53 L42.27,7.12 Z" id="Fill-2"/><path d="M53.13,7.12 L53.13,8.92 L50.57,8.92 L50.57,16.62 C50.57,16.86 50.59,17.05 50.63,17.20 C50.67,17.34 50.75,17.46 50.87,17.54 C50.99,17.62 51.15,17.67 51.35,17.69 C51.56,17.71 51.83,17.73 52.16,17.73 L53.13,17.73 L53.13,19.53 L51.51,19.53 C50.97,19.53 50.50,19.49 50.11,19.42 C49.72,19.35 49.41,19.22 49.17,19.02 C48.93,18.83 48.75,18.56 48.64,18.21 C48.52,17.86 48.46,17.39 48.46,16.81 L48.46,8.92 L46.28,8.92 L46.28,7.12 L48.46,7.12 L48.46,3.39 L50.57,3.39 L50.57,7.12 L53.13,7.12" id="Fill-3"/><path d="M64.53,18.73 C63.57,19.44 62.36,19.79 60.91,19.79 C59.89,19.79 59.00,19.63 58.25,19.31 C57.49,18.99 56.86,18.54 56.35,17.97 C55.84,17.39 55.45,16.70 55.20,15.90 C54.94,15.10 54.80,14.23 54.76,13.29 C54.76,12.34 54.91,11.48 55.21,10.69 C55.51,9.91 55.92,9.23 56.46,8.65 C57.00,8.08 57.63,7.63 58.37,7.31 C59.11,6.99 59.91,6.83 60.79,6.83 C61.93,6.83 62.87,7.05 63.63,7.51 C64.38,7.97 64.98,8.55 65.44,9.25 C65.89,9.96 66.20,10.73 66.38,11.56 C66.55,12.39 66.62,13.18 66.59,13.93 L56.99,13.93 C56.98,14.48 57.04,14.99 57.19,15.48 C57.34,15.97 57.58,16.40 57.91,16.78 C58.24,17.16 58.66,17.46 59.18,17.68 C59.69,17.90 60.29,18.01 60.98,18.01 C61.88,18.01 62.61,17.82 63.18,17.41 C63.75,17.02 64.13,16.41 64.31,15.59 L66.39,15.59 C66.11,16.98 65.49,18.03 64.53,18.73 L64.53,18.73 Z M64.02,10.76 C63.83,10.33 63.58,9.96 63.25,9.65 C62.93,9.34 62.55,9.09 62.11,8.90 C61.68,8.72 61.19,8.63 60.66,8.63 C60.12,8.63 59.62,8.72 59.19,8.90 C58.75,9.09 58.37,9.34 58.06,9.66 C57.75,9.98 57.50,10.35 57.32,10.78 C57.13,11.20 57.03,11.65 56.99,12.13 L64.36,12.13 C64.32,11.65 64.21,11.20 64.02,10.76 L64.02,10.76 Z" id="Fill-4"/><path d="M70.95,7.12 L70.95,8.80 L71.00,8.80 C71.35,8.11 71.89,7.61 72.64,7.30 C73.38,6.98 74.20,6.83 75.09,6.83 C76.09,6.83 76.95,7.00 77.68,7.36 C78.42,7.71 79.03,8.18 79.52,8.78 C80.01,9.38 80.37,10.08 80.62,10.86 C80.87,11.65 80.99,12.48 80.99,13.36 C80.99,14.24 80.87,15.07 80.63,15.85 C80.39,16.64 80.03,17.32 79.54,17.91 C79.06,18.49 78.44,18.95 77.71,19.29 C76.97,19.62 76.12,19.79 75.14,19.79 C74.83,19.79 74.48,19.76 74.09,19.70 C73.70,19.63 73.32,19.53 72.94,19.38 C72.56,19.24 72.20,19.04 71.86,18.79 C71.52,18.55 71.23,18.24 71.00,17.87 L70.95,17.87 L70.95,24.26 L68.84,24.26 L68.84,7.12 L70.95,7.12 L70.95,7.12 Z M78.53,11.52 C78.37,10.97 78.13,10.48 77.81,10.04 C77.49,9.61 77.07,9.27 76.57,9.01 C76.06,8.76 75.47,8.63 74.79,8.63 C74.08,8.63 73.48,8.76 72.98,9.04 C72.49,9.31 72.08,9.67 71.77,10.10 C71.46,10.55 71.23,11.05 71.09,11.60 C70.95,12.17 70.88,12.73 70.88,13.31 C70.88,13.92 70.95,14.51 71.10,15.07 C71.25,15.64 71.48,16.14 71.81,16.57 C72.13,17.01 72.55,17.35 73.06,17.62 C73.57,17.88 74.19,18.01 74.92,18.01 C75.65,18.01 76.25,17.88 76.74,17.61 C77.23,17.34 77.62,16.98 77.92,16.53 C78.22,16.08 78.43,15.57 78.56,14.99 C78.70,14.41 78.76,13.82 78.76,13.21 C78.76,12.64 78.68,12.07 78.53,11.52 L78.53,11.52 Z" id="Fill-5"/><path d="M83.17,10.78 C83.44,9.98 83.84,9.30 84.36,8.71 C84.89,8.13 85.55,7.67 86.33,7.33 C87.10,7.00 87.99,6.83 89.00,6.83 C90.03,6.83 90.92,7.00 91.69,7.33 C92.46,7.67 93.11,8.13 93.64,8.71 C94.17,9.30 94.56,9.98 94.83,10.78 C95.09,11.57 95.23,12.42 95.23,13.33 C95.23,14.25 95.09,15.09 94.83,15.88 C94.56,16.66 94.17,17.35 93.64,17.93 C93.11,18.52 92.46,18.97 91.69,19.30 C90.92,19.63 90.03,19.79 89.00,19.79 C87.99,19.79 87.10,19.63 86.33,19.30 C85.55,18.97 84.89,18.52 84.36,17.93 C83.84,17.35 83.44,16.66 83.17,15.88 C82.91,15.09 82.78,14.25 82.78,13.33 C82.78,12.42 82.91,11.57 83.17,10.78 L83.17,10.78 Z M85.32,15.34 C85.53,15.92 85.81,16.41 86.18,16.80 C86.54,17.19 86.96,17.49 87.45,17.70 C87.94,17.91 88.46,18.01 89.00,18.01 C89.55,18.01 90.06,17.91 90.55,17.70 C91.04,17.49 91.46,17.19 91.83,16.80 C92.19,16.41 92.48,15.92 92.68,15.34 C92.89,14.75 92.99,14.09 92.99,13.33 C92.99,12.58 92.89,11.91 92.68,11.33 C92.48,10.75 92.19,10.25 91.83,9.85 C91.46,9.45 91.04,9.15 90.55,8.94 C90.06,8.73 89.55,8.63 89.00,8.63 C88.46,8.63 87.94,8.73 87.45,8.94 C86.96,9.15 86.54,9.45 86.18,9.85 C85.81,10.25 85.53,10.75 85.32,11.33 C85.11,11.91 85.01,12.58 85.01,13.33 C85.01,14.09 85.11,14.75 85.32,15.34 L85.32,15.34 Z" id="Fill-6"/><path d="M97.83,7.12 L99.94,7.12 L99.94,19.53 L97.83,19.53 L97.83,7.12 Z" id="Fill-7"/><path d="M105.19,7.12 L105.19,9.08 L105.24,9.08 C106.12,7.58 107.51,6.83 109.41,6.83 C110.25,6.83 110.95,6.94 111.51,7.16 C112.08,7.39 112.53,7.70 112.88,8.10 C113.23,8.50 113.47,8.98 113.61,9.53 C113.75,10.08 113.82,10.69 113.82,11.36 L113.82,19.53 L111.71,19.53 L111.71,11.12 C111.71,10.36 111.48,9.75 111.02,9.30 C110.56,8.85 109.92,8.63 109.11,8.63 C108.46,8.63 107.91,8.72 107.43,8.92 C106.96,9.11 106.57,9.38 106.26,9.73 C105.94,10.08 105.71,10.50 105.55,10.97 C105.39,11.44 105.32,11.96 105.32,12.52 L105.32,19.53 L103.21,19.53 L103.21,7.12 L105.19,7.12" id="Fill-8"/><path d="M122.47,7.12 L122.47,8.92 L119.92,8.92 L119.92,16.62 C119.92,16.86 119.94,17.05 119.98,17.20 C120.02,17.34 120.10,17.46 120.22,17.54 C120.33,17.62 120.49,17.67 120.70,17.69 C120.91,17.71 121.18,17.73 121.50,17.73 L122.47,17.73 L122.47,19.53 L120.86,19.53 C120.32,19.53 119.85,19.49 119.46,19.42 C119.07,19.35 118.76,19.22 118.52,19.02 C118.28,18.83 118.10,18.56 117.99,18.21 C117.87,17.86 117.81,17.39 117.81,16.81 L117.81,8.92 L115.63,8.92 L115.63,7.12 L117.81,7.12 L117.81,3.39 L119.92,3.39 L119.92,7.12 L122.47,7.12" id="Fill-9"/><path d="M0.67,11.87 L2.62,13.76 L8.76,19.50 L11.41,16.94 C11.65,16.61 11.64,16.16 11.36,15.84 L8.94,13.72 L8.95,13.72 L6.34,11.19 C6.04,10.84 6.04,10.32 6.37,9.99 L13.38,3.21 L10.20,0.12 L0.68,9.31 C-0.05,10.02 -0.05,11.17 0.67,11.87" id="Fill-10"/><path d="M21.60,15.15 L19.66,13.27 L13.51,7.53 L10.86,10.08 C10.62,10.42 10.64,10.87 10.91,11.19 L13.33,13.31 L13.33,13.31 L15.93,15.84 C16.24,16.19 16.23,16.70 15.91,17.04 L8.89,23.82 L12.08,26.91 L19.65,19.60 L21.60,17.71 C22.33,17.01 22.33,15.86 21.60,15.15" id="Fill-11"/><path d="M44.55,4.58 C44.55,5.24 44.00,5.77 43.32,5.77 C42.64,5.77 42.09,5.24 42.09,4.58 C42.09,3.93 42.64,3.39 43.32,3.39 C44.00,3.39 44.55,3.93 44.55,4.58" id="Fill-12"/><path d="M100.11,4.58 C100.11,5.24 99.56,5.77 98.88,5.77 C98.20,5.77 97.65,5.24 97.65,4.58 C97.65,3.93 98.20,3.39 98.88,3.39 C99.56,3.39 100.11,3.93 100.11,4.58" id="Fill-13"/></g></svg></a><button class="header-module--toggler--2389f h-full px-gs-1 border-transparent bg-transparent lg:hidden" type="button" data-toggle="main-nav" aria-label="Toggle navigation" aria-controls="main-nav"><span aria-hidden="true" class="relative block w-10 rounded-lg bg-white"/></button><ul id="main-nav" class="header-module--navbarItems--065d7 absolute mb-0 left-0 flex grow flex-col justify-end w-full list-none transform origin-top-center transition translate-y-0 ease-in z-40 bg-gray-700 lg:bg-spBlack invisible scale-y-0 opacity-0 lg:static lg:flex lg:flex-row lg:h-full lg:w-auto lg:items-center lg:visible lg:opacity-100 lg:transform-none"><li><div class="mx-auto w-full lg:hidden"><div class="relative w-full lg:max-w-lg"><form class="relative flex bg-transparent text-white lg:text-primary-900 m-0 z-50"><input type=" text" class="bg-gray-500 lg:bg-transparent border rounded-sm border-gray-400 focus:border-primary-400 px-2.5 py-1.5 lg:py-1 m-1 w-full text-sm focus:outline-none " placeholder="Search articles..." aria-label="Search articles" value="" maxlength="100"/></form><div class="&#10; hidden"><div class="ais-Hits Hits searchBox-module--searchHits--c141f"><ul class="ais-Hits-list"/></div></div></div></div></li><li class="flex items-center py-1 lg:text-base lg:px-gs-1 lg:relative lg:h-full"><a href="/blog/" data-text="Blog" class="header-module--navLink--a00a5 inline-block px-3 relative w-full font-bold cursor-pointer text-white hover:text-primary-300 focus:text-gray-200 hover:bg-gray-500 hover:lg:bg-spBlack">Blog</a></li><li><ul class="mb-0 ml-3"><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/premium/library/free/?ref_source=blog_nav">Free Tech Books →</a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/ai/">AI →</a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/javascript/">JavaScript </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/computing/">Computing </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/design-ux/">Design &amp; UX </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/html-css/">HTML &amp; CSS </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/entrepreneur/">Entrepreneur </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/web/">Web </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/php/">PHP </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/wordpress/">WordPress </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/mobile/">Mobile </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/programming/">Programming </a></li><li class="lg:hidden hover:bg-gray-500 cursor-pointer pl-3"><a class="grow-0 font-light text-gray-200 focus:text-primary-300 hover:text-primary-200 w-full inline-block" href="/python/">Python →</a></li></ul></li><li class="flex items-center py-1 lg:text-base lg:px-gs-1 lg:relative lg:h-full"><a href="/community/" data-text="Forum" class="header-module--navLink--a00a5 inline-block px-3 relative w-full font-bold cursor-pointer text-white hover:text-primary-300 focus:text-gray-200 hover:bg-gray-500 hover:lg:bg-spBlack">Forum</a></li><li class="flex items-center py-1 lg:text-base lg:px-gs-1 lg:relative lg:h-full"><a href="/premium/library/" data-text="Library" class="header-module--navLink--a00a5 inline-block px-3 relative w-full font-bold cursor-pointer text-white hover:text-primary-300 focus:text-gray-200 hover:bg-gray-500 hover:lg:bg-spBlack">Library</a></li><li class="flex items-center py-1 lg:text-base lg:px-gs-1 lg:relative lg:h-full"><a href="/premium/sign-in/" data-text="Login" class="header-module--navLink--a00a5 inline-block px-3 relative w-full font-bold cursor-pointer text-white hover:text-primary-300 focus:text-gray-200 hover:bg-gray-500 hover:lg:bg-spBlack">Login</a></li><li class="flex items-center lg:hidden"><a href="/premium/pricing/?ref_source=sitepoint&amp;ref_medium=topnav" id="mobile-join-premium" name="Join Premium" class="text-white font-bold bg-primary-700 rounded-sm p-2 leading-tight hover:bg-primary-400 hover:text-white">Join Premium</a></li></ul><a id="join-premium" href="/premium/pricing/?ref_source=sitepoint&amp;ref_medium=topnav" class="hidden font-bold py-2 mx-4 btn btn-primary lg:block">Join Premium</a></div></nav></header><main id="main-content" class="wj3nsdq" style="--wj3nsdq-0:0 auto;--wj3nsdq-1:none;--wj3nsdq-2:none"><nav class="hidden border-b-2 lg:flex justify-center bg-white"><div class="flex lg:justify-between w-full max-w-screen-xl px-1"><div class="flex justify-center transform transition-transform duration-7000 ease invisible scale-x-0 w-0 m-0 hidden"><div class="relative w-full lg:max-w-lg"><form class="relative flex bg-transparent text-white lg:text-primary-900 m-0 z-50"><input type=" text" class="bg-gray-500 lg:bg-transparent border rounded-sm border-gray-400 focus:border-primary-400 px-2.5 py-1.5 lg:py-1 m-1 w-full text-sm focus:outline-none " placeholder="Search articles..." aria-label="Search articles" value="" maxlength="100"/></form><div class="&#10; hidden"><div class="ais-Hits Hits searchBox-module--searchHits--c141f"><ul class="ais-Hits-list"/></div></div></div><div class="right-8 px-3 pt-1"><button><svg aria-hidden="true" focusable="false" data-prefix="far" data-icon="times" class="svg-inline--fa fa-times fa-w-10 fa-lg categoryNavigation-module--hoverIcon--8248f" role="img" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 320 512"><path fill="currentColor" d="M207.6 256l107.72-107.72c6.23-6.23 6.23-16.34 0-22.58l-25.03-25.03c-6.23-6.23-16.34-6.23-22.58 0L160 208.4 52.28 100.68c-6.23-6.23-16.34-6.23-22.58 0L4.68 125.7c-6.23 6.23-6.23 16.34 0 22.58L112.4 256 4.68 363.72c-6.23 6.23-6.23 16.34 0 22.58l25.03 25.03c6.23 6.23 16.34 6.23 22.58 0L160 303.6l107.72 107.72c6.23 6.23 16.34 6.23 22.58 0l25.03-25.03c6.23-6.23 6.23-16.34 0-22.58L207.6 256z"/></svg></button></div></div><div class="flex overflow-x-hidden ml-0 relative opacity-100 -ml-2 w-full"><button class="categoryNavigation-module--hoverIcon--8248f absolute left-2 z-10 px-3"><svg aria-hidden="true" focusable="false" data-prefix="fas" data-icon="chevron-left" class="svg-inline--fa fa-chevron-left fa-w-10 " role="img" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 320 512"><path fill="currentColor" d="M34.52 239.03L228.87 44.69c9.37-9.37 24.57-9.37 33.94 0l22.67 22.67c9.36 9.36 9.37 24.52.04 33.9L131.49 256l154.02 154.75c9.34 9.38 9.32 24.54-.04 33.9l-22.67 22.67c-9.37 9.37-24.57 9.37-33.94 0L34.52 272.97c-9.37-9.37-9.37-24.57 0-33.94z"/></svg></button><div class="relative overflow-hidden grow w-full mx-1"><ul id="carousel" class="categoryNavigation-module--ul--09a8f m-1 flex transition-all duration-3000 ease" style="transform:translateX(0px)"><li id="Free Tech Books" class="categoryNavigation-module--li--86367"><a href="/premium/library/free/?ref_source=blog_nav" class="categoryNavigation-module--li--86367 text-primary-700">Free Tech Books</a></li><li id="AI" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/ai/">AI</a></li><li id="JavaScript" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/javascript/">JavaScript</a></li><li id="Computing" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/computing/">Computing</a></li><li id="Design &amp; UX" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/design-ux/">Design &amp; UX</a></li><li id="HTML &amp; CSS" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/html-css/">HTML &amp; CSS</a></li><li id="Entrepreneur" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/entrepreneur/">Entrepreneur</a></li><li id="Web" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/web/">Web</a></li><li id="PHP" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/php/">PHP</a></li><li id="WordPress" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/wordpress/">WordPress</a></li><li id="Mobile" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/mobile/">Mobile</a></li><li id="Programming" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/programming/">Programming</a></li><li id="Python" class="categoryNavigation-module--li--86367"><a class="categoryNavigation-module--li--86367 text-black" href="/python/">Python</a></li></ul><span class="categoryNavigation-module--rightFade--71501"/><span class="categoryNavigation-module--leftFade--a3344"/></div><button class="categoryNavigation-module--hoverIcon--8248f absolute right-2 z-10 px-3"><svg aria-hidden="true" focusable="false" data-prefix="fas" data-icon="chevron-right" class="svg-inline--fa fa-chevron-right fa-w-10 " role="img" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 320 512"><path fill="currentColor" d="M285.476 272.971L91.132 467.314c-9.373 9.373-24.569 9.373-33.941 0l-22.667-22.667c-9.357-9.357-9.375-24.522-.04-33.901L188.505 256 34.484 101.255c-9.335-9.379-9.317-24.544.04-33.901l22.667-22.667c9.373-9.373 24.569-9.373 33.941 0L285.475 239.03c9.373 9.372 9.373 24.568.001 33.941z"/></svg></button></div><button class="right-8 px-3 ml-4 categoryNavigation-module--hoverIcon--8248f "><svg aria-hidden="true" focusable="false" data-prefix="fas" data-icon="search" class="svg-inline--fa fa-search fa-w-16 " role="img" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 512 512"><path fill="currentColor" d="M505 442.7L405.3 343c-4.5-4.5-10.6-7-17-7H372c27.6-35.3 44-79.7 44-128C416 93.1 322.9 0 208 0S0 93.1 0 208s93.1 208 208 208c48.3 0 92.7-16.4 128-44v16.3c0 6.4 2.5 12.5 7 17l99.7 99.7c9.4 9.4 24.6 9.4 33.9 0l28.3-28.3c9.4-9.4 9.4-24.6.1-34zM208 336c-70.7 0-128-57.2-128-128 0-70.7 57.2-128 128-128 70.7 0 128 57.2 128 128 0 70.7-57.2 128-128 128z"/></svg></button></div></nav><article aria-label="Article title: How to Use JSON Data Fields in MySQL Databases" class="cG9zdDoxMjkyNzU="><div class="block w-full max-w-screen-xl p-6 mx-auto lg:items-stretch lg:flex lg:flex-row lg:pt-8"><div class="w-full lg:basis-[calc(100%_-_400px)] lg:w-[calc(100%_-_400px)] lg:px-2 lg:min-w-0"><header class="overflow-hidden mx-auto mb-10 w-full sm:px-4 "><div class="flex flex-end mb-0"><a class="text-primary-700 font-bold hover:text-primary-500" href="/programming/">Programming</a></div><h1 class="font-bold leading-10 mb-4 mt-1">How to Use JSON Data Fields in MySQL Databases</h1><ul class="flex flex-row flex-wrap text-sm mb-3 font-bold"><a class="py-1 px-2 mr-2 mb-2 bg-gray-200 text-black hover:text-primary-700 hover:bg-primary-100 font-bold text-sm" href="/programming/databases/">Databases</a><a class="py-1 px-2 mr-2 mb-2 bg-gray-200 text-black hover:text-primary-700 hover:bg-primary-100 font-bold text-sm" href="/web/open-source/">Open Source</a></ul><div class="space-y-4 sm:space-y-0 sm:flex sm:justify-between"><div class="flex items-center relative"><a class="Header-module--imgContainer--90276 z-10" href="/author/craig-buckler/"><img src="https://secure.gravatar.com/avatar/439aeaff7de2bae365adc3eb4947b44d?s=96&amp;#038;d=mm&amp;#038;r=g" class="Header-module--img--e5104"/></a><div class="ml-4 mb-0"><address class="not-italic -mb-2"><a class="Header-module--link--313d0" href="/author/craig-buckler/">Craig Buckler</a></address><time class="text-sm" datetime="2021-03-24">March 24, 2021</time></div></div><div class="flex items-center"><span class="font-bold mr-2">Share</span><button aria-label="Share SitePoint on facebook" data-network="facebook" class="social-share-button mr-2 cursor-pointer transform transition duration-200 hover:scale-125 text-black"><svg aria-hidden="true" focusable="false" data-prefix="fab" data-icon="facebook-square" class="svg-inline--fa fa-facebook-square fa-w-14 fa-lg share-icon" role="img" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 448 512"><path fill="currentColor" d="M400 32H48A48 48 0 0 0 0 80v352a48 48 0 0 0 48 48h137.25V327.69h-63V256h63v-54.64c0-62.15 37-96.48 93.67-96.48 27.14 0 55.52 4.84 55.52 4.84v61h-31.27c-30.81 0-40.42 19.12-40.42 38.73V256h68.78l-11 71.69h-57.78V480H400a48 48 0 0 0 48-48V80a48 48 0 0 0-48-48z"/></svg></button><button aria-label="Share SitePoint on reddit" data-network="reddit" class="social-share-button mr-2 cursor-pointer transform transition duration-200 hover:scale-125 text-black"><svg aria-hidden="true" focusable="false" data-prefix="fab" data-icon="reddit" class="svg-inline--fa fa-reddit fa-w-16 fa-lg share-icon" role="img" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 512 512"><path fill="currentColor" d="M201.5 305.5c-13.8 0-24.9-11.1-24.9-24.6 0-13.8 11.1-24.9 24.9-24.9 13.6 0 24.6 11.1 24.6 24.9 0 13.6-11.1 24.6-24.6 24.6zM504 256c0 137-111 248-248 248S8 393 8 256 119 8 256 8s248 111 248 248zm-132.3-41.2c-9.4 0-17.7 3.9-23.8 10-22.4-15.5-52.6-25.5-86.1-26.6l17.4-78.3 55.4 12.5c0 13.6 11.1 24.6 24.6 24.6 13.8 0 24.9-11.3 24.9-24.9s-11.1-24.9-24.9-24.9c-9.7 0-18 5.8-22.1 13.8l-61.2-13.6c-3-.8-6.1 1.4-6.9 4.4l-19.1 86.4c-33.2 1.4-63.1 11.3-85.5 26.8-6.1-6.4-14.7-10.2-24.1-10.2-34.9 0-46.3 46.9-14.4 62.8-1.1 5-1.7 10.2-1.7 15.5 0 52.6 59.2 95.2 132 95.2 73.1 0 132.3-42.6 132.3-95.2 0-5.3-.6-10.8-1.9-15.8 31.3-16 19.8-62.5-14.9-62.5zM302.8 331c-18.2 18.2-76.1 17.9-93.6 0-2.2-2.2-6.1-2.2-8.3 0-2.5 2.5-2.5 6.4 0 8.6 22.8 22.8 87.3 22.8 110.2 0 2.5-2.2 2.5-6.1 0-8.6-2.2-2.2-6.1-2.2-8.3 0zm7.7-75c-13.6 0-24.6 11.1-24.6 24.9 0 13.6 11.1 24.6 24.6 24.6 13.8 0 24.9-11.1 24.9-24.6 0-13.8-11-24.9-24.9-24.9z"/></svg></button><button aria-label="Share SitePoint on twitter" data-network="twitter" class="social-share-button mr-2 cursor-pointer transform transition duration-200 hover:scale-125 text-black"><svg aria-hidden="true" focusable="false" data-prefix="fab" data-icon="twitter-square" class="svg-inline--fa fa-twitter-square fa-w-14 fa-lg share-icon" role="img" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 448 512"><path fill="currentColor" d="M400 32H48C21.5 32 0 53.5 0 80v352c0 26.5 21.5 48 48 48h352c26.5 0 48-21.5 48-48V80c0-26.5-21.5-48-48-48zm-48.9 158.8c.2 2.8.2 5.7.2 8.5 0 86.7-66 186.6-186.6 186.6-37.2 0-71.7-10.8-100.7-29.4 5.3.6 10.4.8 15.8.8 30.7 0 58.9-10.4 81.4-28-28.8-.6-53-19.5-61.3-45.5 10.1 1.5 19.2 1.5 29.6-1.2-30-6.1-52.5-32.5-52.5-64.4v-.8c8.7 4.9 18.9 7.9 29.6 8.3a65.447 65.447 0 0 1-29.2-54.6c0-12.2 3.2-23.4 8.9-33.1 32.3 39.8 80.8 65.8 135.2 68.6-9.3-44.5 24-80.6 64-80.6 18.9 0 35.9 7.9 47.9 20.7 14.8-2.8 29-8.3 41.6-15.8-4.9 15.2-15.2 28-28.8 36.1 13.2-1.4 26-5.1 37.8-10.2-8.9 13.1-20.1 24.7-32.9 34z"/></svg></button><button aria-label="Share SitePoint on linkedin" data-network="linkedin" class="social-share-button mr-2 cursor-pointer transform transition duration-200 hover:scale-125 text-black"><svg aria-hidden="true" focusable="false" data-prefix="fab" data-icon="linkedin" class="svg-inline--fa fa-linkedin fa-w-14 fa-lg share-icon" role="img" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 448 512"><path fill="currentColor" d="M416 32H31.9C14.3 32 0 46.5 0 64.3v383.4C0 465.5 14.3 480 31.9 480H416c17.6 0 32-14.5 32-32.3V64.3c0-17.8-14.4-32.3-32-32.3zM135.4 416H69V202.2h66.5V416zm-33.2-243c-21.3 0-38.5-17.3-38.5-38.5S80.9 96 102.2 96c21.2 0 38.5 17.3 38.5 38.5 0 21.3-17.2 38.5-38.5 38.5zm282.1 243h-66.4V312c0-24.8-.5-56.7-34.5-56.7-34.6 0-39.9 27-39.9 54.9V416h-66.4V202.2h63.7v29.2h.9c8.9-16.8 30.6-34.5 62.9-34.5 67.2 0 79.7 44.3 79.7 101.9V416z"/></svg></button><button aria-label="Share SitePoint on email" data-network="email" class="social-share-button mr-2 cursor-pointer transform transition duration-200 hover:scale-125 text-black"><svg aria-hidden="true" focusable="false" data-prefix="fas" data-icon="envelope" class="svg-inline--fa fa-envelope fa-w-16 fa-lg share-icon" role="img" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 512 512"><path fill="currentColor" d="M502.3 190.8c3.9-3.1 9.7-.2 9.7 4.7V400c0 26.5-21.5 48-48 48H48c-26.5 0-48-21.5-48-48V195.6c0-5 5.7-7.8 9.7-4.7 22.4 17.4 52.1 39.5 154.1 113.6 21.1 15.4 56.7 47.8 92.2 47.6 35.7.3 72-32.8 92.3-47.6 102-74.1 131.6-96.3 154-113.7zM256 320c23.2.4 56.6-29.2 73.4-41.4 132.7-96.3 142.8-104.7 173.4-128.7 5.8-4.5 9.2-11.5 9.2-18.9v-19c0-26.5-21.5-48-48-48H48C21.5 64 0 85.5 0 112v19c0 7.4 3.4 14.3 9.2 18.9 30.6 23.9 40.7 32.4 173.4 128.7 16.8 12.2 50.2 41.8 73.4 41.4z"/></svg></button></div></div></header><section aria-label="Article content for How to Use JSON Data Fields in MySQL Databases" class="Body-module--section--154bb post-body space-y-6"><div> <p><strong>My article “<a style="font-weight: bold;" href="https://www.sitepoint.com/sql-vs-nosql-differences/">SQL vs NoSQL: The Differences</a>” noted that the line between SQL and NoSQL databases has become increasingly blurred, with each camp adopting features from the other. MySQL 5.7+ InnoDB databases and PostgreSQL 9.2+ both directly support JSON document types in a single field. In this article, we’ll examine the MySQL 8.0 JSON implementation in more detail.</strong></p> <p>Note that any database will accept JSON documents as a single string blob. However, MySQL and PostgreSQL support validated JSON data in real key/value pairs rather than a basic string.</p> <div class="inline-ad"/><h2 id="justbecauseyoucanstorejsonhellip">Just Because You Can Store JSON …</h2> <p><em>… it doesn’t follow you should.</em></p> <p>Normalization is a technique used to optimize the database structure. The First Normal Form (1NF) rule governs that every column should hold a single value — which is clearly broken by storing multi-value JSON documents.</p> <p>If you have clear relational data requirements, use appropriate single-value fields. JSON should be used sparingly as a last resort. JSON value fields can’t be indexed, so avoid using it on columns which are updated or searched regularly. In addition, fewer client applications support JSON and the technology is newer, so it could be less stable than other types.</p> <p>That said, there are good JSON use cases for sparsely populated data or custom attributes.</p> </div><h2 id="createatablewithajsonfield">Create a Table With a JSON Field</h2> <p>Consider a shop selling books. All books have an ID, ISBN, title, publisher, number of pages and other clear relational data. Presume you want to add any number of category tags to each book. You could achieve this in SQL using:</p> <li>a <strong>tag</strong> table which stored each tag name with a unique ID, and</li> <li>a <strong>tagmap</strong> table with many-to-many records mapping book IDs to tag IDs</li> <p>It’ll work, but it’s cumbersome and considerable effort for a minor feature. Therefore, you can define a <strong>tags</strong> JSON field in your MySQL database’s <strong>book</strong> table:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>book<span class="token punctuation">`</span> <span class="token punctuation">(</span> <span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">MEDIUMINT</span><span class="token punctuation">(</span><span class="token number">8</span><span class="token punctuation">)</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>title<span class="token punctuation">`</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>tags<span class="token punctuation">`</span> JSON <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">INNODB</span><span class="token punctuation">;</span> <p>Note that JSON columns can’t have a default value, be used as a primary key, be used as a foreign key, or have an index. You can create <a href="https://dev.mysql.com/doc/refman/en/create-table-secondary-indexes.html">secondary indexes on generated virtual columns</a>, but it’s easier and more practical to retain a value in a separate field if indexes are required.</p> <div class="custom-content"/><h2 id="addingjsondata">Adding JSON Data</h2> <p>Whole JSON documents can be passed in <strong>INSERT</strong> or <strong>UPDATE</strong> statements. For example, our book tags can be passed as an array (inside a string):</p> <pre class="language-sql"><code class="sql language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token punctuation">`</span>book<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>title<span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token punctuation">`</span>tags<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span> <span class="token string">'ECMAScript 2015: A SitePoint Anthology'</span><span class="token punctuation">,</span> <span class="token string">'["JavaScript", "ES2015", "JSON"]'</span> <p>JSON can also be created with these:</p> <p><a href="https://dev.mysql.com/doc/refman/en/json-creation-functions.html#function_json-array">JSON_ARRAY() function</a>, which creates arrays. For example:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token comment">-- returns [1, 2, "abc"]:</span> <span class="token keyword">SELECT</span> JSON_ARRAY<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'abc'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <p><a href="https://dev.mysql.com/doc/refman//en/json-creation-functions.html#function_json-object">JSON_OBJECT() function</a>, which creates objects. For example:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token comment">-- returns {"a": 1, "b": 2}:</span> <span class="token keyword">SELECT</span> JSON_OBJECT<span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'b'</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <p><a href="https://dev.mysql.com/doc/refman//en/json-creation-functions.html#function_json-quote">JSON_QUOTE() function</a>, which quotes a string as a JSON value. For example:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token comment">-- returns "[1, 2, \"abc\"]":</span> <span class="token keyword">SELECT</span> JSON_QUOTE<span class="token punctuation">(</span><span class="token string">'[1, 2, "abc"]'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <p>or you can <code>(CAST anyValue AS JSON)</code>.</p> <p>The <a href="https://dev.mysql.com/doc/refman//en/json-attribute-functions.html#function_json-type">JSON_TYPE() function</a> allows you to check JSON value types. It should return OBJECT, ARRAY, a scalar type (INTEGER, BOOLEAN, etc), NULL, or an error. For example:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token comment">-- returns ARRAY:</span> <span class="token keyword">SELECT</span> JSON_TYPE<span class="token punctuation">(</span><span class="token string">'[1, 2, "abc"]'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- returns OBJECT:</span> <span class="token keyword">SELECT</span> JSON_TYPE<span class="token punctuation">(</span><span class="token string">'{"a": 1, "b": 2}'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- returns an error:</span> <span class="token keyword">SELECT</span> JSON_TYPE<span class="token punctuation">(</span><span class="token string">'{"a": 1, "b": 2'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <p>The <a href="https://dev.mysql.com/doc/refman//en/json-attribute-functions.html#function_json-valid">JSON_VALID() function</a> returns 1 if the JSON is valid or 0 otherwise:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token comment">-- returns 1:</span> <span class="token keyword">SELECT</span> JSON_TYPE<span class="token punctuation">(</span><span class="token string">'[1, 2, "abc"]'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- returns 1:</span> <span class="token keyword">SELECT</span> JSON_TYPE<span class="token punctuation">(</span><span class="token string">'{"a": 1, "b": 2}'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- returns 0:</span> <span class="token keyword">SELECT</span> JSON_TYPE<span class="token punctuation">(</span><span class="token string">'{"a": 1, "b": 2'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <p>Attempting to insert an invalid JSON document will raise an error and the whole record will not be inserted/updated.</p> </div><h2 id="searchingjsondata">Searching JSON Data</h2> <p>The <a href="https://dev.mysql.com/doc/refman//en/json-search-functions.html#function_json-contains">JSON_CONTAINS() function</a> accepts the JSON document being searched and another to compare against. It returns 1 when a match is found. For example:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token comment">-- all books with the 'JavaScript' tag:</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>book<span class="token punctuation">`</span> <span class="token keyword">WHERE</span> JSON_CONTAINS<span class="token punctuation">(</span>tags<span class="token punctuation">,</span> <span class="token string">'["JavaScript"]'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <p>The similar <a href="https://dev.mysql.com/doc/refman//en/json-search-functions.html#function_json-search">JSON_SEARCH() function</a> returns the path to the given match or NULL when there’s no match. It’s passed the JSON document being searched, <code>'one'</code> to find the first match, or <code>'all'</code> to find all matches, and a search string (where <code>%</code> matches any number of characters and <code>_</code> matches one character in an identical way to <a href="https://dev.mysql.com/doc/refman//en/string-comparison-functions.html#operator_like"><code>LIKE</code></a>). For example:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token comment">-- all books with tags starting 'Java':</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>book<span class="token punctuation">`</span> <span class="token keyword">WHERE</span> JSON_SEARCH<span class="token punctuation">(</span>tags<span class="token punctuation">,</span> <span class="token string">'one'</span><span class="token punctuation">,</span> <span class="token string">'Java%'</span><span class="token punctuation">)</span> <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span> <div class="custom-content"/><h2 id="jsonpaths">JSON Paths</h2> <p>A JSON path targets values and can be used to extract or modify parts of a JSON document. The <a href="https://dev.mysql.com/doc/refman//en/json-search-functions.html#function_json-extract">JSON_EXTRACT() function</a> demonstrates this by extracting one or more values:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token comment">-- returns "SitePoint":</span> <span class="token keyword">SELECT</span> JSON_EXTRACT<span class="token punctuation">(</span><span class="token string">'{"id": 1, "website": "SitePoint"}'</span><span class="token punctuation">,</span> <span class="token string">'$.website'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <p>All path definitions start with a <code>$</code> followed by other selectors:</p> <li>a period followed by a name, such as <code>$.website</code></li> <li><code>[N]</code> where N is the position in a zero-indexed array</li> <li>the <code>.[*]</code> wildcard evaluates all members of an object</li> <li>the <code>[*]</code> wildcard evaluates all members of an array</li> <li>the <code>prefix**suffix</code> wildcard evaluates to all paths that begin with the named prefix and end with the named suffix</li> <p>The following examples refer to the following JSON document:</p> <span class="token string">"a"</span>: <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">"b"</span>: <span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">"c"</span>: <span class="token punctuation">[</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string">"d"</span>: { <span class="token string">"e"</span>: <span class="token number">5</span><span class="token punctuation">,</span> <span class="token string">"f"</span>: <span class="token number">6</span> <p>Example paths:</p> <li><code>$.a</code> returns <code>1</code></li> <li><code>$.c</code> returns <code>[3, 4]</code></li> <li><code>$.c[1]</code> returns <code>4</code></li> <li><code>$.d.e</code> returns <code>5</code></li> <li><code>$**.e</code> returns <code>[5]</code></li> </div><h2 id="extractingjsonpathsinqueries">Extracting JSON Paths in Queries</h2> <p>You could extract the name and first tag of your <strong>book</strong> table using the query:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token keyword">SELECT</span> title<span class="token punctuation">,</span> tags<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">"$[0]"</span> <span class="token keyword">AS</span> <span class="token punctuation">`</span>tag1<span class="token punctuation">`</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>book<span class="token punctuation">`</span><span class="token punctuation">;</span> <p>For a more complex example, presume you have a <strong>user</strong> table with JSON profile data. For example:</p> <th>profile</th> <p>You can extract the Twitter name using a JSON path. For example:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token keyword">SELECT</span> name<span class="token punctuation">,</span> profile<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">"$.twitter"</span> <span class="token keyword">AS</span> <span class="token punctuation">`</span>twitter<span class="token punctuation">`</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span><span class="token keyword">user</span><span class="token punctuation">`</span><span class="token punctuation">;</span> <p>You could use a JSON path in the WHERE clause to only return users with a Twitter account:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token keyword">SELECT</span> name<span class="token punctuation">,</span> profile<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">"$.twitter"</span> <span class="token keyword">AS</span> <span class="token punctuation">`</span>twitter<span class="token punctuation">`</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span><span class="token keyword">user</span><span class="token punctuation">`</span> <span class="token keyword">WHERE</span> profile<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">"$.twitter"</span> <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span> <div class="custom-content"/><h2 id="modifyingpartofajsondocument">Modifying Part of a JSON Document</h2> <p>There are several <a href="https://dev.mysql.com/doc/refman//en/json-modification-functions.html">MySQL functions to modify parts of a JSON document</a> using path notation. These include:</p> <li><code>JSON_SET(doc, path, val[, path, val]...)</code>: inserts or updates data in the document</li> <li><code>JSON_INSERT(doc, path, val[, path, val]...)</code>: inserts data into the document</li> <li><code>JSON_REPLACE(doc, path, val[, path, val]...)</code>: replaces data in the document</li> <li><code>JSON_MERGE(doc, doc[, doc]...)</code>: merges two or more document</li> <li><code>JSON_ARRAY_APPEND(doc, path, val[, path, val]...)</code>: appends values to the end of an array</li> <li><code>JSON_ARRAY_INSERT(doc, path, val[, path, val]...)</code>: inserts an array within the document</li> <li><code>JSON_REMOVE(doc, path[, path]...)</code>: removes data from the document</li> <p>You can therefore add a “technical” tag to any book which already has a “JavaScript” tag:</p> <pre class="language-sql"><code class="sql language-sql"><span class="token keyword">UPDATE</span> <span class="token punctuation">`</span>book<span class="token punctuation">`</span> <span class="token keyword">SET</span> tags <span class="token operator">=</span> JSON_MERGE<span class="token punctuation">(</span>tags<span class="token punctuation">,</span> <span class="token string">'["technical"]'</span><span class="token punctuation">)</span> <span class="token keyword">WHERE</span> JSON_SEARCH<span class="token punctuation">(</span>tags<span class="token punctuation">,</span> <span class="token string">'one'</span><span class="token punctuation">,</span> <span class="token string">'JavaScript'</span><span class="token punctuation">)</span> <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span> </div><h2 id="furtherinformation">Further Information</h2> <p>The MySQL manual provides further information about the <a href="https://dev.mysql.com/doc/refman/en/json.html">JSON data type</a> and the <a href="https://dev.mysql.com/doc/refman//en/json-functions.html">associated JSON functions</a>.</p> <p>Again, I urge you not to use JSON unless it’s absolutely necessary. You could emulate an entire document-oriented NoSQL database in MySQL, but it would negate many <a href="https://www.sitepoint.com/sql-vs-nosql-choose/">benefits of SQL</a>, and you may as well switch to a real NoSQL system! That said, JSON data types might save effort for more obscure data requirements within an SQL application.</p> <div class="custom-content"/><h2 id="h-faqs-on-working-with-json-data-in-mysql">FAQs on Working with JSON Data in MySQL</h2>