blocks|key|223345|text|Postgres有针对jsonb的搜索操作符?，但这需要您递归地搜索json内容。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|223346|一种可能的方法，尽管不是非常有效的方法，可以对对象进行字符串化，并使用LIKE搜索它：|223347|myjsonb::text+LIKE+'%25"5cbffeb7-8d5e-4b52-a475-3cf320b2cee9"%25'
myjsonb::text+LIKE+'%25"'+%7C%7C+myuuid+%7C%7C+'"%25'|code-block|syntax|javascript|223348|DB+Fiddle演示|223349|entityMap|0|LINK|mutability|MUTABLE|url|https://www.db-fiddle.com/f/9qp9hymJw2HwVi4tLirnn9/0^0|M|1|0|Z|4|0|0|0|B|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|D|@$9|14|A|15|1|16]]|E|$]]|$1|O|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

Postgres has search operator <code>?</code> for jsonb, but that would require you to search the json content recursively.

A possible, although not very efficient method, would to stringify the object and use <code>LIKE</code> to search it:

<pre><code>myjsonb::text LIKE '%"5cbffeb7-8d5e-4b52-a475-3cf320b2cee9"%'
myjsonb::text LIKE '%"' || myuuid || '"%'
</code></pre>

<a href="https://www.db-fiddle.com/f/9qp9hymJw2HwVi4tLirnn9/0" rel="nofollow noreferrer">Demo on DB Fiddle</a>:

blocks|key|2242544|text|?的问题在于它只考虑顶级键(包括数组元素)，而不考虑值，而不考虑嵌套对象。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|BOLD|entityRanges|data|2242545|您似乎在寻找任何级别上的值、和数组元素(而不是键)。您可以在json(b)列的顶部搜索全文：|2242546|SELECT+*+FROM+tbl
WHERE++to_tsvector('simple',+jsonb_column)
++++@@+tsquery+'5cbffeb7-8d5e-4b52-a475-3cf320b2cee9';|code-block|syntax|javascript|2242547|db<>fiddle+https://dbfiddle.uk/?rdbms=postgres_12&fiddle=0184927f32db682a28c9a5667a290b7a|2242548|to_tsvector()在所有级别上提取值和数组元素--这正是您所需要的。|2242549|需要Postgres+10或更高版本。Postgres+11中的json(b)_to_tsvector()提供了更多的灵活性。|2242550|这对于具有非平凡大小的表很有吸引力，因为全文索引可以非常有效地支持它：|2242551|CREATE+INDEX+tbl_jsonb_column_fts_gin_idx+ON+tbl+USING+GIN+(to_tsvector('simple',+jsonb_column));|2242552|我在示例中使用了'simple'文本搜索配置。您可能需要一个特定于语言的系统，比如'english'。当您只查找UUID字符串时并不重要，但是特定语言的词干可能会使索引变得更小一些.|2242553|相关信息：|2242554|类似于平面jsonb数组元素的查询|unordered-list-item|2242555|Does+the+phrase+search+operator+<->+work+with+JSONB+documents+or+only+relational+tables?|2242556|当您只查找UUID时，可以使用自定义(IMMUTABLE)函数进一步优化，以便将UUID从JSON文档中提取为数组(uuid[])，并在其之上构建函数GIN索引。(但指数要小得多。)然后：|2242557|SELECT+*+FROM+tbl
WHERE++my_uuid_extractor(jsonb_column)+@>+'{5cbffeb7-8d5e-4b52-a475-3cf320b2cee9}';|2242558|这样的函数可能很昂贵，但是对于存储和操作预先计算的值的函数索引来说并不重要。|2242559|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSONB-OP-TABLE|1|https://dbfiddle.uk/?rdbms=postgres_12&fiddle=0184927f32db682a28c9a5667a290b7a|2|https://stackoverflow.com/questions/50101530/like-query-on-elements-of-flat-jsonb-array/50111177#50111177|3|https://stackoverflow.com/questions/57763662/does-the-phrase-search-operator-work-with-jsonb-documents-or-only-relational/57763853#57763853^0|0|1|C|1|Q|1|0|1|0|0|C|2|F|4|U|4|Z|1|0|0|B|26|1|0|0|D|0|W|L|0|M|2|0|0|8|8|15|9|0|0|0|H|2|0|0|2G|3|0|J|9|1M|6|0|0|0^^$0|@$1|2|3|4|5|6|7|1Q|8|@$9|1R|A|1S|B|C]|$9|1T|A|1U|B|D]|$9|1V|A|1W|B|D]]|E|@$9|1X|A|1Y|1|1Z]]|F|$]]|$1|G|3|H|5|6|7|20|8|@$9|21|A|22|B|D]|$9|23|A|24|B|D]|$9|25|A|26|B|C]|$9|27|A|28|B|C]]|E|@]|F|$]]|$1|I|3|J|5|K|7|29|8|@]|E|@]|F|$L|M]]|$1|N|3|O|5|6|7|2A|8|@]|E|@$9|2B|A|2C|1|2D]]|F|$]]|$1|P|3|Q|5|6|7|2E|8|@$9|2F|A|2G|B|C]]|E|@]|F|$]]|$1|R|3|S|5|6|7|2H|8|@$9|2I|A|2J|B|C]]|E|@]|F|$]]|$1|T|3|U|5|6|7|2K|8|@$9|2L|A|2M|B|D]]|E|@]|F|$]]|$1|V|3|W|5|K|7|2N|8|@]|E|@]|F|$L|M]]|$1|X|3|Y|5|6|7|2O|8|@$9|2P|A|2Q|B|C]|$9|2R|A|2S|B|C]]|E|@]|F|$]]|$1|Z|3|10|5|6|7|2T|8|@]|E|@]|F|$]]|$1|11|3|12|5|13|7|2U|8|@]|E|@$9|2V|A|2W|1|2X]]|F|$]]|$1|14|3|15|5|13|7|2Y|8|@]|E|@$9|2Z|A|30|1|31]]|F|$]]|$1|16|3|17|5|6|7|32|8|@$9|33|A|34|B|C]|$9|35|A|36|B|C]]|E|@]|F|$]]|$1|18|3|19|5|K|7|37|8|@]|E|@]|F|$L|M]]|$1|1A|3|1B|5|6|7|38|8|@]|E|@]|F|$]]|$1|1C|3|-4|5|6|7|39|8|@]|E|@]|F|$]]]|1D|$1E|$5|1F|1G|1H|F|$1I|1J]]|1K|$5|1F|1G|1H|F|$1I|1L]]|1M|$5|1F|1G|1H|F|$1I|1N]]|1O|$5|1F|1G|1H|F|$1I|1P]]]]

The problem with the <a href="https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSONB-OP-TABLE" rel="nofollow noreferrer">jsonb operator <code>?</code></a> is that it only considers top-level keys (including array elements), not values, and no nested objects.

You seem to be looking for values and array elements (not keys) on any level. You can get that with a full text search on top of your <code>json</code>(<code>b</code>) column:

<pre><code>SELECT * FROM tbl
WHERE to_tsvector('simple', jsonb_column)
 @@ tsquery '5cbffeb7-8d5e-4b52-a475-3cf320b2cee9';
</code></pre>

db&lt;>fiddle <a href="https://dbfiddle.uk/?rdbms=postgres_12&amp;fiddle=0184927f32db682a28c9a5667a290b7a" rel="nofollow noreferrer">here</a>

<code>to_tsvector()</code> extracts values and array elements on all levels - just what you need.

Requires Postgres 10 or later. <code>json(b)_to_tsvector()</code> in Postgres 11 offers more flexibility. 

That's attractive for tables of non-trivial size as it can be supported with a full text index very efficiently:

<pre><code>CREATE INDEX tbl_jsonb_column_fts_gin_idx ON tbl USING GIN (to_tsvector('simple', jsonb_column));
</code></pre>

I use the <code>'simple'</code> text search configuration in the example. You might want a language-specific one, like <code>'english'</code>. Doesn't matter much while you only look for UUID strings, but stemming for a particular language might make the index a bit smaller ...

Related:

<ul>
<li><a href="https://stackoverflow.com/questions/50101530/like-query-on-elements-of-flat-jsonb-array/50111177#50111177">LIKE query on elements of flat jsonb array</a></li>
<li><a href="https://stackoverflow.com/questions/57763662/does-the-phrase-search-operator-work-with-jsonb-documents-or-only-relational/57763853#57763853">Does the phrase search operator &lt;-&gt; work with JSONB documents or only relational tables?</a></li>
</ul>

While you are only looking for UUIDs, you might optimize further with a custom (<code>IMMUTABLE</code>) function to extract UUIDs from the JSON document as array (<code>uuid[]</code>) and build a functional GIN index on top of it. (Considerably smaller index, yet.) Then:

<pre><code>SELECT * FROM tbl
WHERE my_uuid_extractor(jsonb_column) @&gt; '{5cbffeb7-8d5e-4b52-a475-3cf320b2cee9}';
</code></pre>

Such a function can be expensive, but does not matter much with a functional index that stores and operates on pre-computed values.

I have a jsonb column which has the unfortunate case of being very unpredictable, in some cases its value may be an array with nested values:

<pre><code>["UserMailer", "applicant_setup_3", ["5cbffeb7-8d5e-4b52-a475-3cf320b2cee9"]]
</code></pre>

Sometimes it will be something with key/values like this:

<pre><code>[{"reference_id": "5cbffeb7-8d5e-4b52-a475-3cf320b2cee9", "job_dictionary": ["StatusUpdater", "FollowTwitterUsersJob"]}]
</code></pre>

Is there a way to write a query which just treats the whole column like text and does a <code>like</code> to see if I can find the uuid in the big text blob? I want to find all the records where a particular uuid string is present in the jsonb column.

The query doesn't need to be fast or efficient.

Query JSONB column for any value where =?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个jsonb列，其不幸的情况是非常不可预测，在某些情况下，它的值可能是一个带有嵌套值的数组：["UserMailer", "applicant_setup_3", ["5cbffeb7-8d5e-4b52-a475-3cf320b2cee9"]]有时，它会有类似的键值/值：[{"reference_id": "5...

问查询JSONB列中的任何值where =？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问查询JSONB列中的任何值where =？EN