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% ?

Have you considered not storing coordinates as JSON? – mustaccio Apr 03 '23 at 18:33 — mustaccio, Apr 03 '23 at 18:33

score 0 · Accepted Answer · answered Apr 03 '23 at 18:38

0

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

answered Apr 03 '23 at 18:38

Charlieface

12,780
13
35

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 Apr 11 '23 at 14:11
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 – Taras Apr 11 '23 at 14:22
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, ... – Charlieface Apr 14 '23 at 00:20

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

1 Answers1

Linked