Forum > Databases

Nosql

<< < (3/4) > >>

Zvoni:

--- Quote from: Prakash on December 05, 2022, 11:22:05 am ---CREATE TABLE "users" (
   "id"   INTEGER NOT NULL,
   "full_name"   TEXT NOT NULL,
   "email"   TEXT NOT NULL,
   "created"   DATE NOT NULL,
   "phone"   TEXT,
   PRIMARY KEY("id")
);

insert into users (full_name,email,created, phone) values("packs","abc@gmail.com" ,
"01-01-2022",json('{"cell":"+491765", "home":"+498973"}'));

update users
   set phone =(select json_replace(users.phone,'$.cell',200) from users where full_name = 'packs')
   where full_name == 'packs';

--- End quote ---
See in red.
DON'T
Use ISO-Format "YYYY-MM-DD"

--- Quote ---where full_name == 'packs';
--- End quote ---
There is an '=' too much.....

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---UPDATE usersSET phone =json_replace(phone,'$.cell',200)WHERE full_name = 'packs'; No need for the Sub-SELECT

Prakash:
THANKS.

Zvoni:
Some suggestions/food for thought:
I see the tablename "users"
Set the Columns "full_name" and "email" to UNIQUE each (not combined!).
Especially since i see that "... WHERE full_name='packs'"
That way a "full_name" as well as an "email" can only be entered/used once

Next: Why use json at all?
This is a "classic" 1:m relation
One user can have multiple phone-numbers, but any phone-number can only belong to one user.

I'd even think about moving "email" to a child-table, since a user can have multiple email-addresses

Prakash:
we want to save unstructured data in our application . which is developed in lazarus.

Zvoni:

--- Quote from: Prakash on December 05, 2022, 12:28:09 pm ---we want to save unstructured data in our application . which is developed in lazarus.

--- End quote ---
OK for the unstructered Data.
But it's got nothing to do, if it's written in Lazarus/FPC or in Sanskrit.

Navigation

[0] Message Index

[#] Next page

[*] Previous page