Question Details

No question body available.

Tags

sql postgresql full-text-search jsonb jsonpath

Answers (1)

Accepted Answer Available
Accepted Answer
July 3, 2026 Score: 3 Rep: 31,446 Quality: Expert Completeness: 100%
  1. You don't actually need to consider the position in the array, or explode it, at all. Accessing a named field on an array counts as accessing it on the elements in it.
  2. The filter operates in the context/level you placed the ? on, without making the target path descend into whatever you referenced inside the parentheses. You can filter on .lang, but then still make the target path lead to .desc instead, after the parentheses.
jsonbpathqueryfirst(data,'$.text?(@.lang=="en").desc')

You can see a match in the second row in this test: demo at dbfiddle

select totsvector('english',jsonbpathqueryfirst(data,'$.text?(@.lang=="en").desc'))
       @@ websearchtotsquery('english', 'whatever') as isamatch
      ,jsonbpathqueryfirst(data,'$.text?(@.lang=="en").desc')
      ,*
from t limit 10;
isamatch jsonbpathqueryfirst id data
f "English description" 1 {"text": [{"desc": "English description", "lang": "en"}, {"desc": "Description en français", "lang": "fr"}, {"desc": "Deutsche Beschreibung", "lang": "de"}], "otherkey": "doesn't matter"}
t "Wherever you go, whatever you do." 2 {"text": [{"desc": "English description", "lang": "zh"}, {"desc": "Wherever you go, whatever you do.", "lang": "en"}, {"desc": "Deutsche Beschreibung", "lang": "br"}], "otherkey": "doesn't matter"}
null null 61089 {"text": [{"desc": "1857929bcf416dfd8ccb68c91bb988c3", "lang": "sk"}, {"desc": "06d09783e675caf60c139163e33ae12f", "lang": "ua"}], "otherkey": "doesn't matter"}
f "dcda20cfad3ced4a288c883fb085f92c" 5392 {"text": [{"desc": "316ead8665350bd0d0a5e009f11afb7f", "lang": "zh"}, {"desc": "938c87ba25989685260ac8a4eefd5b97", "lang": "ua"}, {"desc": "e2936fe7fc939df4555a39cdb15cc317", "lang": "de"}, {"desc": "dcda20cfad3ced4a288c883fb085f92c", "lang": "en"}, {"desc": "b80aae0d46db875bee7cfc75e80226d1", "lang": "zh"}, {"desc": "7403e51a34b1e0e78386d738679d14ae", "lang": "ua"}, {"desc": "842e37b47d4aadefdb071fa48e571a2e", "lang": "br"}, {"desc": "734c66a9b735baaf3aba2431f8eea63c", "lang": "ua"}, {"desc": "b7f347b6373011021839d36899dc3084", "lang": "sk"}, {"desc": "feb7cb18aa95aaf0fa9597839a9e7188", "lang": "fr"}, {"desc": "8cbe22babe02557f7d00452f18576512", "lang": "zh"}], "otherkey": "doesn't matter"}

For completeness, here's the index and the query with an explain analyze confirming it uses it, as intended (don't forget to analyze the table after building it):

create index i on t using gin (
  totsvector('english', jsonbpathqueryfirst(data,'$.text?(@.lang=="en").desc')));
vacuum analyze t;
explain analyze verbose
select * from t
where totsvector('english', jsonbpathqueryfirst(data,'$.text?(@.lang=="en").desc'))
  @@ websearchtotsquery('english', 'whatever')
QUERY PLAN
Bitmap Heap Scan on public.t (cost=12.00..16.27 rows=1 width=401) (actual time=0.032..0.034 rows=1 loops=1)
Output: id, data
Recheck Cond: (totsvector('english'::regconfig, jsonbpathqueryfirst(t.data, '$."text"?(@."lang" == "en")."desc"'::jsonpath, '{}'::jsonb, false)) @@ '''whatev'''::tsquery)
Heap Blocks: exact=1
-> Bitmap Index Scan on i (cost=0.00..12.00 rows=1 width=0) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: (totsvector('english'::regconfig, jsonbpathqueryfirst(t.data, '$."text"?(@."lang" == "en")."desc"'::jsonpath, '{}'::jsonb, false)) @@ '''whatev'''::tsquery)
Planning Time: 0.298 ms
Execution Time: 0.060 ms

If you remember to always use an additional condition, you could also make that a partial index:

create index i on t using gin (
  totsvector('english', jsonbpathqueryfirst(data,'$.text?(@.lang=="en").desc')))
where data @? '$.text.lang=="en"';

The reason the array.key access works is that by default JSONPath expressions use lax mode:

Lax mode facilitates matching of a JSON document and path expression when the JSON data does not conform to the expected schema. If an operand does not match the requirements of a particular operation, it can be automatically wrapped as an SQL/JSON array, or unwrapped by converting its elements into an SQL/JSON sequence before performing the operation. Also, comparison operators automatically unwrap their operands in lax mode, so you can compare SQL/JSON arrays out-of-the-box.