{"id":3222,"date":"2024-04-24T14:01:53","date_gmt":"2024-04-24T07:01:53","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/sqlite-tutorial\/sqlite-json\/"},"modified":"2024-04-25T21:24:37","modified_gmt":"2024-04-25T14:24:37","slug":"sqlite-json","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-json\/","title":{"rendered":"SQLite JSON"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to store JSON data in an SQLite database and utilize various SQLite JSON functions to effectively manipulate it.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to SQLite JSON data type<\/h2>\n\n\n\n<p>SQLite does not have a built-in JSON data type. However, you can use the <code>TEXT<\/code> data type to store JSON data. <\/p>\n\n\n\n<p>Additionally, SQLite provides various built-in <a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-json-functions\/\">JSON functions<\/a> and operators to allow you to effectively manipulate JSON data.<\/p>\n\n\n\n<p>For example, the following statement <a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-create-table\/\">creates a table<\/a> called <code>products<\/code> to store the product data:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> products (\n    <span class=\"hljs-keyword\">id<\/span>      <span class=\"hljs-built_in\">INTEGER<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n    <span class=\"hljs-keyword\">name<\/span>    <span class=\"hljs-built_in\">TEXT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    details <span class=\"hljs-built_in\">TEXT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In the <code>products<\/code> table:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>id<\/code> is the <a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-primary-key\/\">primary key column<\/a>.<\/li>\n\n\n\n<li><code>name<\/code> column stores the product names.<\/li>\n\n\n\n<li><code>details<\/code> column uses the <code>TEXT<\/code> data type to store JSON data.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Inserting JSON data into the database<\/h3>\n\n\n\n<p>The following <code>INSERT<\/code> statement inserts JSON data into <code>details<\/code> columns of the <code>products<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> products (<span class=\"hljs-keyword\">name<\/span>, details)\n<span class=\"hljs-keyword\">VALUES<\/span> \n    (<span class=\"hljs-string\">'iPhone 13'<\/span>, <span class=\"hljs-string\">'{\"category\": \"Electronics\", \"price\": 999, \"colors\": &#91;\"Black\", \"Blue\", \"White\"]}'<\/span>),\n    (<span class=\"hljs-string\">'Samsung Galaxy S21'<\/span>, <span class=\"hljs-string\">'{\"category\": \"Electronics\", \"price\": 899, \"colors\": &#91;\"Phantom Black\", \"Phantom Silver\"]}'<\/span>),\n    (<span class=\"hljs-string\">'Nike Air Force 1'<\/span>, <span class=\"hljs-string\">'{\"category\": \"Shoes\", \"price\": 100, \"colors\": &#91;\"White\", \"Black\"]}'<\/span>),\n    (<span class=\"hljs-string\">'Adidas Ultraboost'<\/span>, <span class=\"hljs-string\">'{\"category\": \"Shoes\", \"price\": 180, \"colors\": &#91;\"Core Black\", \"Cloud White\"]}'<\/span>),\n    (<span class=\"hljs-string\">'MacBook Pro'<\/span>, <span class=\"hljs-string\">'{\"category\": \"Electronics\", \"price\": 1299, \"colors\": &#91;\"Silver\", \"Space Gray\"]}'<\/span>),\n    (<span class=\"hljs-string\">'Amazon Kindle'<\/span>, <span class=\"hljs-string\">'{\"category\": \"Electronics\", \"price\": 79, \"colors\": &#91;\"Black\"]}'<\/span>),\n    (<span class=\"hljs-string\">'Sony PlayStation 5'<\/span>, <span class=\"hljs-string\">'{\"category\": \"Electronics\", \"price\": 499, \"colors\": &#91;\"White\"]}'<\/span>),\n    (<span class=\"hljs-string\">'Cuisinart Coffee Maker'<\/span>, <span class=\"hljs-string\">'{\"category\": \"Home &amp; Kitchen\", \"price\": 99, \"colors\": &#91;\"Stainless Steel\", \"Black\"]}'<\/span>),\n    (<span class=\"hljs-string\">'Dyson V11 Vacuum Cleaner'<\/span>, <span class=\"hljs-string\">'{\"category\": \"Home &amp; Kitchen\", \"price\": 599, \"colors\": &#91;\"Iron\", \"Nickel\"]}'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Here&#8217;s the contents of the <code>products<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> products;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">id | name                     | details\n<span class=\"hljs-comment\">---+--------------------------+-----------------------------------------------------------------------------------------<\/span>\n1  | iPhone 13                | {\"category\": \"Electronics\", \"price\": 999, \"colors\": &#91;\"Black\", \"Blue\", \"White\"]}\n2  | Samsung Galaxy S21       | {\"category\": \"Electronics\", \"price\": 899, \"colors\": &#91;\"Phantom Black\", \"Phantom Silver\"]}\n3  | Nike Air Force 1         | {\"category\": \"Shoes\", \"price\": 100, \"colors\": &#91;\"White\", \"Black\"]}\n4  | Adidas Ultraboost        | {\"category\": \"Shoes\", \"price\": 180, \"colors\": &#91;\"Core Black\", \"Cloud White\"]}\n5  | MacBook Pro              | {\"category\": \"Electronics\", \"price\": 1299, \"colors\": &#91;\"Silver\", \"Space Gray\"]}\n6  | Amazon Kindle            | {\"category\": \"Electronics\", \"price\": 79, \"colors\": &#91;\"Black\"]}\n7  | Sony PlayStation 5       | {\"category\": \"Electronics\", \"price\": 499, \"colors\": &#91;\"White\"]}\n8  | Cuisinart Coffee Maker   | {\"category\": \"Home &amp; Kitchen\", \"price\": 99, \"colors\": &#91;\"Stainless Steel\", \"Black\"]}\n9  | Dyson V11 Vacuum Cleaner | {\"category\": \"Home &amp; Kitchen\", \"price\": 599, \"colors\": &#91;\"Iron\", \"Nickel\"]}\n\n(9 rows)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Extracting values from JSON data<\/h3>\n\n\n\n<p>To extract a value from JSON data, you use the <code><a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-json-functions\/sqlite-json_extract-function\/\">json_extract()<\/a><\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">json_extract(json, path)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code>json_extract()<\/code> function extracts a value from JSON data using a specified path. The path locates the value in the JSON data you want to extract.<\/p>\n\n\n\n<p>The following statement uses the <code>json_extract()<\/code> function to extract the price from JSON data stored in the <code>details<\/code> column of the <code>products<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  <span class=\"hljs-keyword\">name<\/span>,\n  json_extract (details, <span class=\"hljs-string\">'$.price'<\/span>) <span class=\"hljs-keyword\">AS<\/span> price\n<span class=\"hljs-keyword\">FROM<\/span>\n  products;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">name                     | price\n<span class=\"hljs-comment\">-------------------------+------<\/span>\niPhone 13                | 999\nSamsung Galaxy S21       | 899\nNike Air Force 1         | 100\nAdidas Ultraboost        | 180\nMacBook Pro              | 1299\nAmazon Kindle            | 79\nSony PlayStation 5       | 499\nCuisinart Coffee Maker   | 99\nDyson V11 Vacuum Cleaner | 599\n\n(9 rows)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Using the json_extract() function in the WHERE clause<\/h3>\n\n\n\n<p>The following statement retrieves the products with the category <code>'Electronics'<\/code>. It compares the value extracted from the JSON data in the <code>details<\/code> column and compares it with the string <code>'Electronics'<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  <span class=\"hljs-keyword\">name<\/span>\n<span class=\"hljs-keyword\">FROM<\/span>\n  products\n<span class=\"hljs-keyword\">WHERE<\/span>\n  json_extract (details, <span class=\"hljs-string\">'$.category'<\/span>) = <span class=\"hljs-string\">'Electronics'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">name\n<span class=\"hljs-comment\">------------------<\/span>\niPhone 13\nSamsung Galaxy S21\nMacBook Pro\nAmazon Kindle\nSony PlayStation 5\n\n(5 rows)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Inserting a JSON value<\/h3>\n\n\n\n<p>To insert a value into a JSON document, you use the <code>json_insert()<\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">json_insert(json, path, value)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code>json_insert()<\/code> function inserts the <code>value<\/code> into the <code>json<\/code> using the specified <code>path<\/code>. If the <code>path<\/code> does not exist, the function creates the element. If the <code>json<\/code> element already exists, the function does not overwrite.<\/p>\n\n\n\n<p>For example, the following statement inserts the <code>stock<\/code> attribute with the value <code>10<\/code> into the JSON document with id 1:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> products\n<span class=\"hljs-keyword\">SET<\/span>\n  details = json_insert (details, <span class=\"hljs-string\">'$.stock'<\/span>, <span class=\"hljs-number\">10<\/span>)\n<span class=\"hljs-keyword\">WHERE<\/span>\n  <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Verify the insert:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> products\n<span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">id | name      | details\n<span class=\"hljs-comment\">---+-----------+------------------------------------------------------------------------------------<\/span>\n1  | iPhone 13 | {\"category\":\"Electronics\",\"price\":999,\"colors\":&#91;\"Black\",\"Blue\",\"White\"],\"stock\":10}\n\n(1 row)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Updating a JSON value<\/h3>\n\n\n\n<p>To update an existing JSON value, you can use the <code>json_replace()<\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">json_replace(json, path, value)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code>json_replace()<\/code> function replaces the value specified by a path in the json data. If the value does not exist, it does not create the value.<\/p>\n\n\n\n<p>For example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> products\n<span class=\"hljs-keyword\">SET<\/span> details = json_replace(details, <span class=\"hljs-string\">'$.stock'<\/span>, <span class=\"hljs-number\">0<\/span>)\n<span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Verify the update:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> products\n<span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-17\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">id | name      | details\n<span class=\"hljs-comment\">---+-----------+-----------------------------------------------------------------------------------<\/span>\n1  | iPhone 13 | {\"category\":\"Electronics\",\"price\":999,\"colors\":&#91;\"Black\",\"Blue\",\"White\"],\"stock\":0}\n\n(1 row)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-17\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Deleting a JSON value<\/h3>\n\n\n\n<p>To remove a json value, you use the <code>json_remove()<\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-18\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">json_remove(json, path)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-18\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>For example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-19\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> products\n<span class=\"hljs-keyword\">SET<\/span> details = json_remove(details, <span class=\"hljs-string\">'$.stock'<\/span>)\n<span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-19\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Verify the delete:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-20\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> products\n<span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-20\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-21\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">id | name      | details\n<span class=\"hljs-comment\">---+-----------+-------------------------------------------------------------------------<\/span>\n1  | iPhone 13 | {\"category\":\"Electronics\",\"price\":999,\"colors\":&#91;\"Black\",\"Blue\",\"White\"]}\n\n(1 row)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-21\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Aggregating data into a JSON array<\/h3>\n\n\n\n<p>To aggregate values into a JSON array, you use the <code><a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-json-functions\/sqlite-json_group_array-function\/\">json_group_array()<\/a><\/code> function. For example, the following statement aggregates the product names with the shoe category into a JSON array:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-22\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> json_group_array(<span class=\"hljs-keyword\">name<\/span>)\n<span class=\"hljs-keyword\">FROM<\/span> products\n<span class=\"hljs-keyword\">WHERE<\/span> json_extract (details, <span class=\"hljs-string\">'$.category'<\/span>) = <span class=\"hljs-string\">'Shoes'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-22\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-23\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">json_group_array(name)\n<span class=\"hljs-comment\">----------------------------------------<\/span>\n&#91;\"Nike Air Force 1\",\"Adidas Ultraboost\"]\n\n(1 row)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-23\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">Aggregating data into a JSON object<\/h3>\n\n\n\n<p>To aggregate values into a JSON object, you use the <code><a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-json-functions\/sqlite-json_group_object-function\/\">json_group_object()<\/a><\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-24\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">json_group_object(name, value)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-24\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>For example, the following statement uses the json_group_object to aggregate product names and their ids in the <code>Shoes<\/code> category into a JSON object:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-25\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> json_group_object(<span class=\"hljs-keyword\">name<\/span>, <span class=\"hljs-keyword\">id<\/span>)\n<span class=\"hljs-keyword\">FROM<\/span> products\n<span class=\"hljs-keyword\">WHERE<\/span> json_extract (details, <span class=\"hljs-string\">'$.category'<\/span>) = <span class=\"hljs-string\">'Shoes'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-25\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-26\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">json_group_object(name, id)\n<span class=\"hljs-comment\">--------------------------------------------<\/span>\n{\"Nike Air Force 1\":3,\"Adidas Ultraboost\":4}\n\n(1 row)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-26\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQLite uses <code>TEXT<\/code> data type to store JSON data.<\/li>\n\n\n\n<li>Use the <code>json_extract()<\/code> function to extract values from JSON data.<\/li>\n\n\n\n<li>Use the <code>json_replace()<\/code> function to replace a value in JSON data.<\/li>\n\n\n\n<li>Use the <code>json_remove()<\/code> function to remove a value from JSON data.<\/li>\n\n\n\n<li>Use the <code>json_group_array()<\/code> function to aggregate values into a JSON array.<\/li>\n\n\n\n<li>Use the <code>json_group_object()<\/code> function to aggregate values into a JSON object.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"3222\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/\"\n\t\t\t\tdata-post-title=\"SQLite JSON\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"3222\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/\"\n\t\t\t\tdata-post-title=\"SQLite JSON\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: in this tutorial, you will learn how to store JSON data in an SQLite database and utilize various SQLite JSON functions to effectively manipulate it. Introduction to SQLite JSON data type SQLite does not have a built-in JSON data type. However, you can use the TEXT data type to store JSON data. Additionally, SQLite [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":2,"menu_order":41,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-3222","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQLite JSON<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will learn how to store JSON data in an SQLite database and utilize various SQLite JSON functions to effectively manipulate it.\" \/>\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.sqlitetutorial.net\/sqlite-json\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLite JSON\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will learn how to store JSON data in an SQLite database and utilize various SQLite JSON functions to effectively manipulate it.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/\" \/>\n<meta property=\"og:site_name\" content=\"SQLite Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2024-04-25T14:24:37+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/#\/schema\/person\/6d69b968cad0102e30d6694ed8dc6427\"},\"headline\":\"SQLite JSON\",\"datePublished\":\"2024-04-24T07:01:53+00:00\",\"dateModified\":\"2024-04-25T14:24:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/\"},\"wordCount\":497,\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/\",\"url\":\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/\",\"name\":\"SQLite JSON\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/#website\"},\"datePublished\":\"2024-04-24T07:01:53+00:00\",\"dateModified\":\"2024-04-25T14:24:37+00:00\",\"description\":\"In this tutorial, you will learn how to store JSON data in an SQLite database and utilize various SQLite JSON functions to effectively manipulate it.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-json\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlitetutorial.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLite Tutorial\",\"item\":\"https:\/\/www.sqlitetutorial.net\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQLite JSON\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/#website\",\"url\":\"https:\/\/www.sqlitetutorial.net\/\",\"name\":\"SQLite Tutorial\",\"description\":\"A Step-by-step SQLite Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlitetutorial.net\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/#\/schema\/person\/6d69b968cad0102e30d6694ed8dc6427\",\"name\":\"admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLite JSON","description":"In this tutorial, you will learn how to store JSON data in an SQLite database and utilize various SQLite JSON functions to effectively manipulate it.","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.sqlitetutorial.net\/sqlite-json\/","og_locale":"en_US","og_type":"article","og_title":"SQLite JSON","og_description":"In this tutorial, you will learn how to store JSON data in an SQLite database and utilize various SQLite JSON functions to effectively manipulate it.","og_url":"https:\/\/www.sqlitetutorial.net\/sqlite-json\/","og_site_name":"SQLite Tutorial","article_modified_time":"2024-04-25T14:24:37+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlitetutorial.net\/sqlite-json\/#article","isPartOf":{"@id":"https:\/\/www.sqlitetutorial.net\/sqlite-json\/"},"author":{"name":"admin","@id":"https:\/\/www.sqlitetutorial.net\/#\/schema\/person\/6d69b968cad0102e30d6694ed8dc6427"},"headline":"SQLite JSON","datePublished":"2024-04-24T07:01:53+00:00","dateModified":"2024-04-25T14:24:37+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlitetutorial.net\/sqlite-json\/"},"wordCount":497,"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.sqlitetutorial.net\/sqlite-json\/","url":"https:\/\/www.sqlitetutorial.net\/sqlite-json\/","name":"SQLite JSON","isPartOf":{"@id":"https:\/\/www.sqlitetutorial.net\/#website"},"datePublished":"2024-04-24T07:01:53+00:00","dateModified":"2024-04-25T14:24:37+00:00","description":"In this tutorial, you will learn how to store JSON data in an SQLite database and utilize various SQLite JSON functions to effectively manipulate it.","breadcrumb":{"@id":"https:\/\/www.sqlitetutorial.net\/sqlite-json\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlitetutorial.net\/sqlite-json\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlitetutorial.net\/sqlite-json\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlitetutorial.net\/"},{"@type":"ListItem","position":2,"name":"SQLite Tutorial","item":"https:\/\/www.sqlitetutorial.net\/"},{"@type":"ListItem","position":3,"name":"SQLite JSON"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlitetutorial.net\/#website","url":"https:\/\/www.sqlitetutorial.net\/","name":"SQLite Tutorial","description":"A Step-by-step SQLite Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlitetutorial.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlitetutorial.net\/#\/schema\/person\/6d69b968cad0102e30d6694ed8dc6427","name":"admin"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/pages\/3222","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/comments?post=3222"}],"version-history":[{"count":5,"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/pages\/3222\/revisions"}],"predecessor-version":[{"id":3297,"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/pages\/3222\/revisions\/3297"}],"up":[{"embeddable":true,"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/pages\/2"}],"wp:attachment":[{"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/media?parent=3222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}