CODEWITHSUNDEEP
jsonpostgresqlpattern-matchingfull-text-searchtsvector
Bikswan
Bikswan

Reputation: 140

Postgres full text search on json

I am dumping a json value into text field as I have some invalid json data that cannot be stored in json/jsonb. I would like to search and highlight "Value16": 16711680 in the following example, but I am also getting a hit on "Value20": 16711680.

{
  "Value01": 122,
  "Value02": 25,
  "Value03": 9,
  "Value04": 538,
  "Value05": false,
  "Value06": 65534,
  "Value07": 2,
  "Value08": 0,
  "Value09": 2,
  "Value10": 1,
  "Value11": 0,
  "Value12": 0,
  "Value13": false,
  "Value14": 0,
  "Value15": 0,
  "Value16": 16711680,
  "Value17": 0,
  "Value18": 0,
  "Value19": 0,
  "Value20": 16711680,
  "Value21": 0,
  "Value22": 0
}

I tried following queries but did not work. Do I need to use any escape characters here?

to_tsquery('Value16<->16711680')

to_tsquery('Value16&16711680')

Basically I would like to hit only if key and value match together.
How to search for a key/value pair in the json text?

Upvotes: 2

Views: 2375

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

Since your key and value are directly adjacent, I would suggest a phrase search (requires Postgres 9.6 or later):

...
WHERE my_tsvector @@ to_tsquery('simple', 'Value16 <-> 16711680');

Or:

...
WHERE my_tsvector @@ phraseto_tsquery('simple','Value16 16711680');

Only qualifies with the two search terms next to each other (ignoring noise characters in between). There is also the <N> variant of the operator for terms a couple of words apart.

Detailed explanation:

You can even combine this with prefix matching:

Upvotes: 4

Related Questions