我的 PostgreSQL 14.7 数据库中有一个表public.polygon_versions
:
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)
);
示例geo_json
数据是:
{"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":{}}
我正在尝试查询字段与geo_json
某些坐标匹配的所有记录。例如:30.504106925781265、50.43731985018662 我试过以下 SQL 查询:
SELECT *
FROM polygon_versions
WHERE geo_json LIKE '%30.504106925781265,50.43731985018662%'
它返回一个错误:
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
不要弄乱 JSON 文档的文本表示。那很昂贵而且完全不可靠。一旦您输入无关紧要的空白或重新排序对象,同一个 JSON 文档就会有无限数量的不同文本表示。
相反
jsonb
,使用contains 运算符@>
:或 SQL/JSON 路径表达式,但上面的更简单且同样高效。
GIN 索引可以支持任何一个,例如:
或者,如果您只需要优化一个查询,一个更专业的表达式索引和查询:
小提琴
有关的:
json
数据类型不是文本,它不适用于like
运算符。如果要查找 json 具有某些值的行,则需要先提取该值。所以查询应该是这样的:
coordinates
如果您实际上是一个字符串,这将起作用。对于 json 允许的操作列表,请阅读文档: https ://www.postgresql.org/docs/9.3/functions-json.html