How to search by index of a jsonb (PostgresQL) document using Rust Diesel?

help
1

I can't figure out how to query a (PostgreSQL) jsonb document by index.

I have the following to create the db schema (in the up.sql):

CREATE TABLE public.photo
(
id BIGSERIAL PRIMARY KEY NOT NULL,
datadoc JSONB NOT NULL,
created_timestamp TIMESTAMP NOT NULL,
modified_timestamp TIMESTAMP NOT NULL
);
CREATE UNIQUE INDEX photo_id_uindex ON public.photo (id);
CREATE INDEX index_tags ON photo USING GIN ((datadoc->'tags') jsonb_path_ops);
CREATE INDEX photo_created_timestamp_uindex ON public.photo (created_timestamp);
CREATE INDEX photo_modified_timestamp_uindex ON public.photo (modified_timestamp);

And, say I'd like to retrieve all datadoc jsonb docs with tags "fashion"...

and I have this code:

let results = photo.filter( ... how to filter here? ... )
    .load::<Photo>(&connection)
    .expect("Error loading photos");

println!("Displaying {} photos", results.len());

but can't figure out how to use the filter function. I tried "like" but it says some of the traits are not satisfied.

I think there are 3 ways to solve my problem:

  1. Figure out how to use the filter() method as described above
  2. Figure out how to use raw select statement
  3. Retrieve all docs, convert to json objects, and then manually filter them (but I think this is not good for performance).

Appreciate if someone could help. Thanks!

2

As for solution 2. which proposed earlier, I looked earlier at Postgres documentation and the SQL statement to execute would be something like this:

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

So, how do I execute a raw SQL statement using Rust Diesel?

3

you can use sql or sql_query (or, create a view)

1 Like
4

Yup, sql is working for me (thanks!), but I'm stuck with another problem.

First, let me explain what I got going:

This:

.filter(sql("datadoc->'tags' ? 'ddd'"))

works (ie it retrieves the results set), but it picks only jsonb documents with exact match and not what I need. I think because this works and thus proves that whatever I put into the filter() method is actually appended as a WHERE clause into the SQL statement - am I right?

That being said, I'm trying to retrieve jsonb documents from a Postgres table and according to documentation, the SQL should be:

.filter(sql("datadoc @> '{\"tags\": [\"ddd\"]}'"))
OR
.filter(sql(r#"datadoc @> '{"tags": ["ddd"]}'"#))

but it picks up 0 results - no errors, but just no results being retreived.

To make clear of my problem: I have a jsonb document stored in a Postgres table and need to retrieve it by filtering using tags.

The document looks like this:

{
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
"name": "Angela Barton",
"is_active": true,
"company": "Magnafone",
"address": "178 Howard Place, Gulf, Washington, 702",
"registered": "2009-11-07T08:53:22 +08:00",
"latitude": 19.793713,
"longitude": 86.513373,
"tags": [
"enim",
"aliquip",
"qui"
]
}

Therefore,

  1. Is there anyway to print out the SQL statement prepared by Diesel to see if it has been altered?
  2. What is the solution to retrieve the jsonb documents?

If I can't get this to work, I'll try sql_query, but I think sql is a cleaner method.

Thanks!

5
  1. I think you want debug_query
  2. Not sure about the jsonb stuff. We support reading to serde_json::Value but for other stuff you might need a custom solution. Best ask on Gitter
1 Like