PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL : to_tsvector on a specific value of a jsonB colonne

I want to make a full search query on a spécific value of my jsonb like the format of the json is :

{
  "highlights": {
    "concise": null,
    "complete": null
  },
  "gist": {
    "concise": [
      {
        "text": "",
        "isHighlighted": false,
        "highlightID": ""
      }...
    ],
    "complete": null
  },
  "summary": {
  },
  "followup": {
  },
  "subjects": {
    ...
}

i want do my research on gist.concise

I have already tri like that but doesn’t work :

SELECT
  audio.id
FROM
  audio
JOIN
  audio_json ON audio.id = audio_json.audio_id
WHERE
  to_tsvector(audio.name) @@ to_tsquery('ornithorinque')
  OR to_tsvector(audio.context::text) @@ to_tsquery('ornithorinque')
  OR to_tsvector(
    audio_json.analysis ->> 'gist' ->> 'concise'::text
  ) @@ to_tsquery('ornithorinque');
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    the answer is: to_tsvector((audio_json.analysis ->> 0)::jsonb ->> 'gist') @@ to_tsquery('ornithorinque')


  2. 0

    Built-in JSONPath you can use with jsonb offers like_regex filter expression: demo

    SELECT audio.id
FROM audio
JOIN audio_json ON audio.id = audio_json.audio_id
WHERE   audio_json.analysis
     @@ '$[0].gist.concise[0].text like_regex "ornithorinque")'

    Here, @@ is jsonb path predicate check operator.

    JSONB is fast on its own and faster with an index, but queries with like_regex won’t qualify for index support. The advantage of Full Text Search tsquery and tsvector is that they will be faster if you extract your text and index it, or build an expression index:

    create index on audio_json 
  using gin(((analysis#>>'{0,gist,concise,0,text}')::tsvector));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search