Also — documenting some more powerful D1 features (thanks to SQLite): https://silverlock-d1-generate

MMatt Silverlock Also — documenting some more powerful D1 features (thanks to SQLite): https://si...

M

D1 pretending to be NoSQL:

CREATE TABLE sensor_readings (
    -- other columns omitted
    location as (json_extract(raw_data, '$.measurement.location')) STORED
    -- now you can create an index on data derived from your JSON data!
);

CREATE TABLE sensor_readings (
    -- other columns omitted
    location as (json_extract(raw_data, '$.measurement.location')) STORED
    -- now you can create an index on data derived from your JSON data!
);

CREATE TABLE sensor_readings (
    -- other columns omitted
    location as (json_extract(raw_data, '$.measurement.location')) STORED
    -- now you can create an index on data derived from your JSON data!
);

CREATE TABLE sensor_readings (
    -- other columns omitted
    location as (json_extract(raw_data, '$.measurement.location')) STORED
    -- now you can create an index on data derived from your JSON data!
);

Original message was deleted

M

Matt SilverlockOP•6/16/23, 5:11 PM

Good call — added to my list! The SQLite docs are extensive, but:

They aren't super approachable to a lot of devs
Assume you know "why" something is useful - e.g. prior RDBMS knowledge
Don't always apply to D1.

One of our goals is to make it clear that D1 is D1 and "when" these features are useful or even necessary. DBs can be needlessly complex.

M

Matt SilverlockOP•6/16/23, 5:11 PM

Original message was deleted

M

Matt SilverlockOP•6/16/23, 5:14 PM

Yeah. Ideally we have an e2e example. Learning pharases/prefixes/etc. is tricky if you haven't dealt with FTS + SQL before.

M

Matt SilverlockOP•6/16/23, 5:15 PM

(Probably some kind of

users

users

table with a

address

address

column that you can search over? That seems fairly "real world" for a lot of D1 use cases. Or an

orders

orders

orders

orders table with a similar approach?)

M

manstfu•6/17/23, 9:37 AM

guys how you handle user preferences with sqlite?

M

manstfu•6/17/23, 9:38 AM

we cant use json

M

manstfu•6/17/23, 9:55 AM

hmm

M

manstfu•6/17/23, 9:57 AM

from where i import those

M

manstfu•6/17/23, 9:58 AM

M

manstfu•6/17/23, 10:39 AM

M

manstfu•6/17/23, 10:40 AM

i use drizzle orm

M

manstfu•6/17/23, 10:41 AM

cloudflare/d1 have no export named json_array

Mmanstfu cloudflare/d1 have no export named json_array

M

Matt SilverlockOP•6/17/23, 10:58 AM

It’s not an exported function and it probably wouldn’t make sense for them to create exports for 20+ JSON functions. You should be able to use

.where(sql

.where(sql

.where(sql

.where(sql…

per https://orm.drizzle.team/docs/sql#sql-in-where

Magical sql operator 🪄 – DrizzleORM

Drizzle ORM | %s

Mmanstfu i use drizzle orm

M

Matt SilverlockOP•6/17/23, 11:00 AM

I’d also recommend joining the Drizzle Discord: https://discord.gg/yfjTbVXMW4

M

manstfu•6/17/23, 11:01 AM

im already there, but there is no active people

Mmanstfu Click to see attachment

R

raise;•6/17/23, 11:40 AM

It's not a js function. you need to include it within your

raw sql query

raw sql query

raw sql query

raw sql query , like sql

'select json_array_length(colname) as x from table'

'select json_array_length(colname) as x from table'

'select json_array_length(colname) as x from table'

'select json_array_length(colname) as x from table'.

M

manstfu•6/17/23, 11:41 AM

@0b1111

M

manstfu•6/17/23, 11:43 AM

M

manstfu•6/17/23, 12:03 PM

Mmanstfu Click to see attachment

R

raise;•6/17/23, 12:06 PM

what is

userTable.settings

userTable.settings

userTable.settings

userTable.settings ?

M

manstfu•6/17/23, 12:07 PM

M

manstfu•6/17/23, 12:08 PM

i want to hold a json with user settings

M

manstfu•6/17/23, 12:08 PM

like avatar, description etc

Mmanstfu Click to see attachment

R

raise;•6/17/23, 12:19 PM

but why u would use json in the first time, just use columns is better

M

manstfu•6/17/23, 12:24 PM

for example

M

manstfu•6/17/23, 12:24 PM

social media link

M

manstfu•6/17/23, 12:24 PM

you want me to create a column for each, facebook, instagram etc?

Rraise;but why u would use json in the first time, just use columns is better 😄

M

manstfu•6/17/23, 12:25 PM

.

Mmanstfu .

R

raise;•6/17/23, 12:32 PM

did you read the doc of sqlite functions ?

M

manstfu•6/17/23, 12:32 PM

https://developers.cloudflare.com/d1/learning/querying-json/

Query JSON · Cloudflare D1 docs

D1 has built-in support for querying and parsing JSON data stored within a database. This enables you to:

R

raise;•6/17/23, 12:32 PM

but did u read it carefully ? because u are trying to use the

json

json

json

json function

M

manstfu•6/17/23, 12:33 PM

R

raise;•6/17/23, 12:33 PM

what does it says ?

M

manstfu•6/17/23, 12:33 PM

that he should return an array

M

manstfu•6/17/23, 12:33 PM

with json

R

raise;•6/17/23, 12:33 PM

Validates the provided string is JSON and returns a minified version of that JSON object.

M

manstfu•6/17/23, 12:34 PM

R

raise;•6/17/23, 12:34 PM

it does not convert and return your json string into object json, its just validate & clean the json and return the minified result of it but at string format

M

manstfu•6/17/23, 12:34 PM

M

manstfu•6/17/23, 12:34 PM

this is with json_array

R

raise;•6/17/23, 12:35 PM

man what you are trying is called

parse/extract json

parse/extract json

parse/extract json

parse/extract json, so u are using the wrong functions

R

raise;•6/17/23, 12:37 PM

Most of these json functions are designed to help you manipulate/store the data.

Mmanstfu Click to see attachment

R

raise;•6/17/23, 12:37 PM

try to use

json_extract

json_extract

json_extract

json_extract

M

manstfu•6/17/23, 12:38 PM

R

raise;•6/17/23, 12:39 PM

json_extract(${usersTable.settings}, '$')

json_extract(${usersTable.settings}, '$')

json_extract(${usersTable.settings}, '$')

json_extract(${usersTable.settings}, '$')

M

manstfu•6/17/23, 12:40 PM

null

M

manstfu•6/17/23, 12:40 PM

and with '$' only

settings
: 
"{\"hello\":[\"world\",\"there\"]}"

settings
: 
"{\"hello\":[\"world\",\"there\"]}"

settings
: 
"{\"hello\":[\"world\",\"there\"]}"

settings
: 
"{\"hello\":[\"world\",\"there\"]}"

Mmanstfu and with '$' only `settings : "{\"hello\":[\"world\",\"there\"]}"`

R

raise;•6/17/23, 12:42 PM

examples

SELECT json_extract('{"hello":["world","there"]}', '$.hello');

SELECT json_extract('{"hello":["world","there"]}', '$.hello');

SELECT json_extract('{"hello":["world","there"]}', '$.hello');

SELECT json_extract('{"hello":["world","there"]}', '$.hello');
->

'["world","there"]'

'["world","there"]'

'["world","there"]'

'["world","there"]'

SELECT json_extract('{"hello":["world","there"]}', '$');

SELECT json_extract('{"hello":["world","there"]}', '$');

SELECT json_extract('{"hello":["world","there"]}', '$');

SELECT json_extract('{"hello":["world","there"]}', '$');
->

'{"hello":["world","there"]}'

'{"hello":["world","there"]}'

'{"hello":["world","there"]}'

'{"hello":["world","there"]}'

Also — documenting some more powerful D1 features (thanks to SQLite): https://silverlock-d1-generate

Similar Threads

Similar Threads

Similar Threads