Filter JSONB column

[feliwir]

Backend

PostgreSQL

Diesel version

2.3.2

Diesel Features

"image","openjp2","jpegxl","charls"

Operating System Version

Ubuntu 25.04

Third party libraries

serde = { version = "1.0.228", features = ["derive"] } serde_json = "1.0.145"

What do you want to do?

I have a database table that has a JSONB columns. That column contains DICOM-tags in form of a DICOM-JSON (see https://dicom.nema.org/medical/dicom/current/output/chtml/part18/sect_f.2.html). See this entry for example:

{
    "00080005": {
        "vr": "CS",
        "Value": [
            "ISO_IR 100"
        ]
    },
    "00080008": {
        "vr": "CS",
        "Value": [
            "ORIGINAL",
            "PRIMARY",
            "AXIAL",
            "CT_SOM5 SPI"
        ]
    },
    "00080016": {
        "vr": "UI",
        "Value": [
            "1.2.840.10008.5.1.4.1.1.2"
        ]
    },
    "00080018": {
        "vr": "UI",
        "Value": [
            "1.2.826.0.1.3680043.2.473.2.3472463830.1692.1087912327.528"
        ]
    },
    "00080021": {
        "vr": "DA",
        "Value": [
            "20010327"
        ]
    },
    "00080022": {
        "vr": "DA",
        "Value": [
            "20010327"
        ]
    },
    "00321060": {
        "vr": "LO",
        "Value": [
            "CT Thorax"
        ]
    }
}

My goal is to query for rows that contain a specific tag. E.g. i filter for a date like this:

"00080022": {
        "vr": "DA",
        "Value": [
            "20010327"
        ]
    },

Compile time error

no compilation issue

What code do you already have?

pub async fn list_with_criteria(
    conn: &mut DbConn,
    offset: i64,
    limit: i64,
    criteria: &InMemDicomObject,
) -> Result<Vec<(Patient, Study)>, diesel::result::Error> {
    use crate::db::schema::study::dsl::*;

    let json = serde_json::to_value(DicomJson::from(criteria)).unwrap();

    study
        .filter(tags.contains(&json))
        .inner_join(crate::db::schema::patient::table)
        .order_by((study_date.desc(), study_time.desc()))
        .offset(offset)
        .limit(limit)
        .select((Patient::as_select(), Study::as_select()))
        .load(conn)
        .await
}


`tags` is my JSONB column in this case. My idea was to use the `contains` method to check if the column contains the json that i am looking for. However this always yields an empty response. How do I filter against a JSONB column correctly?

Additional details

No response

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

[weiznich]

So I tried it locally on a psql command line with a test table and the provided data.

The following query works for me:

select * from study where tags @> '{"00080022": {
        "vr": "DA",
        "Value": [
            "20010327"
        ]
    }}'

This is also what is constructed by your DSL (beside the join/limit/offset);

I do not know which data are in your table or what other constraints exists so I can only guess why the query is returning no results for you. The most likely reason is that the INNER JOIN filters out all results for whatever reason.

You can and should check if the query looks correct via diesel::debug_query(). That should also allow you to inspect which part of the query is filtering out results.

[feliwir]

Apparently it seems to work now and the content of my rows just didn't match (i was 100% sure i checked), For completeness sake a statement now looks like this:

SELECT "study"."study_instance_uid", "study"."patient_uid", "study"."study_date", "study"."study_time", "study"."accession_number", "study"."study_description", "study"."institution", "study"."tags", "study"."created_at", "study"."updated_at", "patient"."uid", "patient"."patient_id", "patient"."patient_name", "patient"."patient_sex", "patient"."patient_birth_date", "patient"."patient_birth_time", "patient"."tags", "patient"."created_at", "patient"."updated_at" FROM ("study" INNER JOIN "patient" ON ("study"."patient_uid" = "patient"."uid")) WHERE ("study"."tags" @> $1) ORDER BY "study"."study_date" DESC, "study"."study_time" DESC LIMIT $2 OFFSET $3 -- binds: [Object {"00100010": Object {"vr": String("PN"), "Value": Array [Object {"Alphabetic": String("Bronchialbaum, baumig")}]}}, 1000, 0]

This statement filters the patientname (00100010) for Bronchialbaum, baumig. A more general SQL question i have now would be if it's possible to filter the table for "partial" JSON tags. E.g. when i wanted to just search for Bronchialbaum (which is only a part of the data inside the JSONB

[weiznich]

Something like the following SQL query would allow you to filter by partial tags:

select study.* from study, jsonb_each(tags) where value->'Value' ? '20010327' group by study.id;

That's not out of the box expressible with diesel's DSL. You would need to write your own extension for that as there is currently no support for set operations.

[feliwir]

Thanks a lot for sharing!