时间戳

首页 > 解决方案 > Alternative to @> '["TEXT"]' syntax

postgresql - 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?

标签： postgresql

解决方案

在你用你的框架或驱动程序解决这个潜在问题之前，你可能会度过一段非常不愉快的时光。

但是你可以像这样解决这个问题：

@> jsonb_build_array('EXAMPLESTRING')

您也可以使用“to_jsonb”函数，但这可能会给您的驱动程序带来更多的语法问题。

推荐阅读