CODEWITHSUNDEEP
postgresql
ThomasReggi
ThomasReggi

Reputation: 59485

Alternative to @> '["TEXT"]' syntax

I have a JSONB column with an array in it. I have a working query below.

However my language-specific implementation is failing because of the use of double quotes around EXAMPLESTRING.

Here is the query:

SELECT
    b0."id",
    b0."base_sku",
    b0."name",
    b0."logo",
    b0."email",
    b0."active",
    b0."building_group_id",
    b0."operating_region_id",
    b0."building_package_id",
    b0."metadata",
    b0."location",
    b0."inserted_at",
    b0."updated_at"
FROM "buildings" AS b0
WHERE (b0."metadata"->'google_place_ids' @> '["EXAMPLESTRING"]')
AND (b0."active" = TRUE)
LIMIT 1

I am looking for an alternative to this part of the query:

@> '["EXAMPLESTRING"]')

Is there any other way to write this?

Upvotes: 0

Views: 23

Answers (1)

jjanes
jjanes

Reputation: 44305

You will probably have a very bad unpleasant time until you fix this underlying problem with your framework or driver.

But you can work around the issue like this:

@> jsonb_build_array('EXAMPLESTRING')

You could also use the 'to_jsonb' function, but that might introduce more syntactic issues for your driver to choke on.

Upvotes: 1

Related Questions