How can I query using like on json field in PostgreSQL?

Question

I've set up the following DB Fiddle: https://dbfiddle.uk/mzsp-4tg

How should I make a correct SQL query to use like on geo_json field of json type to get table records with string matching coordinates combination %30.504106925781265,50.43731985018662% ?

• mustaccio: Have you considered not storing coordinates as JSON?

Answer 1

If you store or convert the column to jsonb then you can use JSONpath

SELECT *
FROM polygon_versions
WHERE jsonb_path_exists(
 geo_json,
 '$.geoJSON.geometry.coordinates[*] ? (@[0] == 30.504106925781265 && @[1] == 50.43731985018662)');

db<>fiddle

• Taras: How can I fix the following ERROR: function jsonb_path_exists(json, unknown) does not exist LINE 3: WHERE jsonb_path_exists( ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 38
• Taras: I have the following PostgreSQL version: PostgreSQL 14.7 (Debian 14.7-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
• Charlieface: Is the column json or jsonb? This only works with jsonb so either change the column's data type, or convert it like this WHERE jsonb_path_exists(geo_json::jsonb, ...