Postgres search JSON by dynamic value-CodePudding

In Postgres 14, I'm trying to query a JSON array element:

{
  "haystack": [
    { "search": "findthis" },
    { "search": "someothervalue" }
  ]
}

This works:

SELECT 1
FROM test
WHERE data->'haystack' @> '[{"search":"findthis"}]';

However, when "findthis" comes from a function: getText() or some other dynamic value, I get 0 results:

SELECT 1
      FROM test
      WHERE data->'haystack' @> to_jsonb('[{"search":"' || getText()::text || '"}]');

(I am expecting to return 1)

My test:

What am I missing?

CodePudding user response：

Use jsonb, not to_jsonb

select to_jsonb('[{"search":"' || 'findthis' || '"}]'), jsonb('[{"search":"' || 'findthis' || '"}]')

and your query with type cast

SELECT 1
      FROM test
      WHERE data->'haystack' @> ('[{"search":"' || 'findthis' || '"}]')::jsonb;