Javascript library, define colum using ::text etc
# help-and-questions
s
In the SQL Editor I can run on a JSONB column
Copy code
SELECT * FROM messages m WHERE m.sources::text LIKE '%"document": 25%';
And works completely fine, I'm trying to replicate this with the js library
Copy code
const { data, error } = await supabase
    .from("messages")
    .select(`*`)
    .like("sources", `%"document": ${id}%]`);
however unsure how I replicate the ``::text`` as like the SQL query
g
You don't need ::text in the js library
assuming sources column is text
s
its jsonb @garyaustin
sorry I should have specified my bad
v
The purpose of JSONB type in postgres is to be able to query into it. However, the page discussing this on Supabase docs only shows how to select them as output, not filter against them: https://supabase.com/docs/guides/database/json
The answer in this SO question shows you can query into it in the filters: https://stackoverflow.com/questions/68406176/supabase-json-query-javascript
You probably want to filter on
sources->>'document' = 25
s
bit difficult as I am trying to reach this:
Copy code
column name: sources

data example:
[{"metadata":{"loc":{"lines":{"to":668,"from":668}},"index":135,"active":true,"chatbots":[2],"document":36}}]
So its like sources->>metadata->>document?
v
You'll want the result of metadata to be a JSON, so
sources->0->metadata->>document
. The ->> gives you a string, and you're inside an array.
Copy code
postgres=> select '[{"metadata":{"loc":{"lines":{"to":668,"from":668}},"index":135,"active":true,"chatbots":[2],"document":36}}]'::jsonb->0->'metadata'->>'document';
 ?column?
----------
 36
(1 row)
s
@vick worked perfect!!
Copy code
const { data, error } = await supabase
    .from("messages")
    .select(`*`)
    .eq("sources->0->metadata->>document", String(23));