在PostgreSQL 9.3+的JSON,它是如此的丑陋?[英] JSON at PostgreSQL 9.3+, it is so ugly?

本文是小编为大家收集整理的关于在PostgreSQL 9.3+的JSON,它是如此的丑陋?的处理方法,想解了在PostgreSQL 9.3+的JSON,它是如此的丑陋?的问题怎么解决?在PostgreSQL 9.3+的JSON,它是如此的丑陋?问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

有一种"不那么难看"的选项可以将字符串作为 texts 并将整数作为 ints?

JSON-tree 的所有叶子都有众所周知的数据类型,那么,如何让 PostgreSQL 看到数据类型?

<小时>

问题的例子和丑陋的解决方案...还有不那么丑陋的解决方案?

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 运算符,它返回一个 JSON 对象字段或一个 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

本文地址:https://www.itbaoku.cn/post/1764196.html