PostgreSQL 9.3+上的JSON,太丑了吗? [英] JSON at PostgreSQL 9.3+, it is so ugly?
本文介绍了PostgreSQL 9.3+上的JSON,太丑了吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有某种不太丑陋的选项可以将字符串作为文本
s和整数作为 int
s?
There are kind of "less ugly" option to get strings as text
s and integers as int
s?
所有 JSON树的叶子都具有众所周知的数据类型,那么,如何对PostgreSQL说看数据类型?
All leaves of a JSON-tree have well known datatypes, so, how to say to PostgreSQL to see the datatypes?
示例和丑陋的解决方案...丑陋的解决方案较少?
Examples of problems and UGLY solutions... There are less ugly solution?
SELECT version(); -- PostgreSQL 9.3
CREATE TABLE example (
id serial PRIMARY KEY,
info JSON
);
INSERT INTO example (info) VALUES
('{"aa":10,"bb":"nonono nononon"}'),
('{"aa":10,"x":"etc"}'),
('{"aa":9,"x":"etc etc"}');
-- SIX ERROS... But human-readable syntax
SELECT DISTINCT info->'aa' FROM example; -- ex.1
SELECT * FROM example ORDER BY info->'aa'; -- ex.2
SELECT info->'x' FROM example WHERE info->'x' IS NOT NULL; -- ex.3
SELECT * FROM example WHERE info->'x'='etc'; -- ex.4
SELECT * FROM example WHERE (info->'x')::text='etc'; -- ex.5
SELECT * FROM example WHERE info->'aa'=10; -- ex.6
-- UGLY (!!) six workarounds
SELECT DISTINCT (info->'aa')::text FROM example; -- ex.1
SELECT * FROM example ORDER BY (info->'aa')::text::int; -- ex.2
SELECT info->'x' FROM example WHERE (info->'x')::text IS NOT NULL; -- ex.3
SELECT * FROM example WHERE trim((info->'x')::text,'"')='etc'; -- exs. 4 and 5
SELECT * FROM example WHERE (info->'aa')::text::int=10; -- ex.6
推荐答案
使用->>
json运算符,它以 text
。
SELECT DISTINCT info->>'aa' FROM example;
SELECT * FROM example ORDER BY info->>'aa';
SELECT info->>'x' FROM example WHERE (info->>'x') IS NOT NULL;
SELECT * FROM example WHERE info->>'x'='etc';
SELECT * FROM example WHERE (info->>'aa')::int=10; -- still needs a cast to int
这篇关于PostgreSQL 9.3+上的JSON,太丑了吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文