如何在PostgreSQL中解析JSON

Question

我有一个包含字符变量列和此列具有json的数据库表。 我需要编写一个查询，将这个json解析成单独的列。

我在这里找到了 json_each 函数，但是我不知道如何使用它。

- Ivan Ursul

---

PostgreSQL自9.2版本开始支持原生JSON数据类型。 - WEBjuju

Answer 1

我搞定了，伙计们。

假如我有一个名为books的表格， 我可以轻松地编写查询。

SELECT 
   id, 
   data::json->'name' as name
FROM books;

它将导致

我还可以尝试获取不存在的列

SELECT 
   id, 
   data::json->'non_existant' as non_existant
FROM books;

在这种情况下，我将获得空结果

- Ivan Ursul

---

如果您使用的是足够新的Postgres版本，使用data::jsonb->'foo'将会稍微更加高效（对于json类型，每次访问元素时实际上会重新解析）。 - Dmitri

在这种情况下，您也可以使用 data::jsonb。只有在将其包装在子查询中并在外部查询中多次引用转换后的结果时，才能获得好处。 - Craig Ringer

如果你不想在字符串周围有双引号，请使用 data::json->>'name'。详见：https://www.postgresql.org/docs/current/functions-json.html - nunespascal

为了去掉引号，可以使用 trim('"' FROM (data::json->'name')::text)。 - Viktor Reinok

有没有解决方案可以处理不知道底层JSON blob结构的情况？ - jayarjo

Answer 2

太棒了，谢谢分享。我发现你可以更深入地探索，比如:

SELECT 
   id, 
   data::json->'name' as name,
   data::json->'author' ->> 'last_name' as author
FROM books;

- hit3k