Given this table:
CREATE TABLE "carts" (
"id" INTEGER NOT NULL,
"products" TEXT NOT NULL,
PRIMARY KEY("id" AUTOINCREMENT)
)
Where the products
column contains text values representing JSON arrays of numbers like [12,13,14]
, how can I insert a single item without duplicates?
Examples
Add 17
to [12,13,14]
to give [12,13,14,17]
.
Add 13
to [12,13,14]
to give [12,13,14]
(no change therefore duplicate avoided).
CodePudding user response:
SQLite does not have a special JSON data type (although you can use it in the definition of a column).
All JSON-like values are strings (data type TEXT
) and you can easily use string functions and the operator LIKE
to check for a pattern in their values:
UPDATE carts
SET products = json_insert(products, '$[#]', ?)
WHERE id = 1
AND REPLACE(REPLACE(products, '[', ','), ']', ',') NOT LIKE '%,' || ? || ',%';
Replace ?
with the value that you want to insert.
See the demo.
CodePudding user response:
WITH
new_product(id, product_id) AS (VALUES (1, 13)),
new_records AS (
SELECT carts.id, json_insert(carts.products, '$[#]', np.product_id) AS products
FROM carts, new_product AS np
WHERE carts.id = np.id
AND NOT (carts.products like np.product_id || ',%'
OR carts.products like '%,' || np.product_id
OR carts.products like '%,' || np.product_id || ',%')
)
UPDATE carts SET products = new_records.products
FROM new_records
WHERE carts.id = new_records.id;
CodePudding user response:
I have found this to be the easiest way:
- Insert the item into the array using the standard function
json_insert
.
> SELECT json_insert('[12,13,14]','$[#]',13) AS tempArray
tempArray
[12,13,14,13]
- Use the table-valued function
json_each
to break apart the array into a temporary table.
> SELECT * FROM (SELECT json_insert('[12,13,14]','$[#]',13) AS tempArray), json_each(tempArray)
tempArray key value type atom id parent fullkey path
[12,13,14,13] 0 12 integer 12 1 $[0] $
[12,13,14,13] 1 13 integer 13 2 $[1] $
[12,13,14,13] 2 14 integer 14 3 $[2] $
[12,13,14,13] 3 13 integer 13 4 $[3] $
- Take only the
value
column (as the others are not needed).
> SELECT value FROM (SELECT json_insert('[12,13,14]','$[#]',13) AS tempArray), json_each(tempArray)
value
12
13
14
13
- Use
DISTINCT
to remove duplicates.
> SELECT DISTINCT value FROM (SELECT json_insert('[12,13,14]','$[#]',13) AS tempArray), json_each(tempArray)
value
12
13
14
- Use the aggregation function
json_group_array
to combine the results in a JSON array text value.
> SELECT json_group_array(DISTINCT value) FROM (SELECT json_insert('[12,13,14]','$[#]',13) AS tempArray), json_each(tempArray)
json_group_array(DISTINCT value)
[12,13,14]
- Stick this statement into an
UPDATE
statement, replacing the example array with a reference to the desired field.
UPDATE carts
SET product = (SELECT json_group_array(DISTINCT value) FROM (SELECT json_insert(carts.product,'$[#]',13) AS tempArray), json_each(tempArray))
WHERE id = 1