Find rows with given coordinates nested in a JSON column

Question

I have a table public.polygon_versions in my PostgreSQL 14.7 database:

CREATE TABLE public.polygon_versions (
 id bigint NOT NULL DEFAULT nextval('polygon_versions_id_seq'::regclass),
 entity_id bigint NOT NULL,
 creation_transaction_id bigint NOT NULL,
 obsolescence_transaction_id bigint,
 geo_json json NOT NULL,
 CONSTRAINT polygon_versions_pkey PRIMARY KEY (id),
 CONSTRAINT polygon_versions_creation_transaction_id_foreign FOREIGN KEY (creation_transaction_id)
 REFERENCES public.transactions (id),
 CONSTRAINT polygon_versions_entity_id_foreign FOREIGN KEY (entity_id)
 REFERENCES public.polygons (id),
 CONSTRAINT polygon_versions_obsolescence_transaction_id_foreign FOREIGN KEY (obsolescence_transaction_id)
 REFERENCES public.transactions (id)
);

Example geo_json data is:

{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[30.3626584543099,50.43004834913466],[30.37021155489584,50.4281893337737],[30.36969657076498,50.433110097683176],[30.369009925257167,50.433984846616404],[30.3626584543099,50.43004834913466]]]},"properties":{}}

I'm trying to query all records where field geo_json matches some coordinates. For example: 30.504106925781265, 50.43731985018662 I've tried the following SQL query:

SELECT * 
FROM polygon_versions 
WHERE geo_json LIKE '%30.504106925781265,50.43731985018662%'

It returns an error:

ERROR: operator does not exist: json ~~ unknown
LINE 1: SELECT * FROM polygon_versions WHERE geo_json LIKE '%30.5041...
 ^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 47

• Erwin Brandstetter: We can't give the best answer while we don't know much about your geo_json column. Show some sample values and tell us what's possible and what's not. Why jsoninstead of jsonb? Is performance important? If so, how many rows, how big is the table, how big is the JSON column on avg?
• Charlieface: Honestly why did I bother writing an answer to your previous question if you just completely ignore it? @ErwinBrandstetter has just basically given you the same answer again in a slightly different way.

Answer 1

Don't mess with the text representation of a JSON document. That's expensive and completely unreliable. The same JSON document has an infinite number of distinct text representations once you throw in insignificant white space or reorder objects.

Work with jsonb instead, and use the contains operator @>:

SELECT * FROM polygon_versions
WHERE geo_json @> '{"geometry":{"coordinates":[[[30.3626584543099,50.43004834913466]]]}}'

Or a SQL/JSON path expression, but the above is simpler and just as efficient.

Either can be supported with a GIN index like:

CREATE INDEX polygon_versions_geo_json_idx ON polygon_versions USING gin (geo_json);

Or, if you only need to optimize the one query, a more specialized expression index & query:

CREATE INDEX polygon_versions_geo_json_expr_idx ON polygon_versions USING gin ((geo_json #> '{geometry,coordinates}'));
SELECT * FROM polygon_versions
WHERE geo_json #> '{geometry,coordinates}' @> '[[[30.3626584543099,50.43004834913466]]]'

fiddle

Related:

• Find rows containing a key in a JSONB array of records

Answer 2

json datatype is not a text, it does not work with like operator.

If you want to find rows where json has some values, you need to extract that value first. So the query should look like:

SELECT * 
FROM polygon_versions 
WHERE geo_json->>coordinates like '%30.504106925781265,50.43731985018662%'

This will work if your coordinates is actually a string.

for list of operations allowed for json read documentation: https://www.postgresql.org/docs/9.3/functions-json.html

• Taras: Unfortunately it does not work for me: [2023年04月11日 19:10:16] Connected zoning-service: public...y, tiger> SELECT * FROM polygon_versions WHERE geo_json->coordinates like '%30.504106925781265,50.43731985018662%' [2023年04月11日 19:10:16] [42703] ERROR: column "coordinates" does not exist [2023年04月11日 19:10:16] Позиция: 48
• Taras: Example geo_json data is: {"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[30.3626584543099,50.43004834913466],[30.37021155489584,50.4281893337737],[30.36969657076498,50.433110097683176],[30.369009925257167,50.433984846616404],[30.3626584543099,50.43004834913466]]]},"properties":{}}
• Taras: How can I query all coordinates in such case?
• Erwin Brandstetter: @Taras: Please put sample values into your question, in legible format.
• user1822: @Taras: you need to use ->> to extract the value as text, not ->