使用PostgreSQL解析JSON轻松工作

方石剑
2,317 阅读16分钟

使用PostgreSQL解析JSON轻松工作

Divyansh Sharmaon数据库管理系统,JSON,PostgreSQL,关系型数据库- 2022年6月10日 -Write for Hevo

数据解析是理解数据和创造有意义的洞察力的关键步骤。它是一个简单的过程，将数据从一种格式转化为另一种格式，使其更具可读性，随后准备进行分析。

自从PostgreSQL 9.2版本发布以来，PostgreSQL已经引入了一些JSON操作符和函数来解析JSON数据。PostgreSQL解析JSON操作可以帮助你和你的工作组将JavaScript对象（如数组和对象）分解成更小的数据块，从而使每个与你的PostgreSQL数据库互动的人都能提高数据可访问性。

在这个Parse JSON PostgreSQL指南中，我们讨论了解析的基础知识，它是什么，以及它如何帮助理解你的JSON文件中编码的信息。你将了解到不同类型的PostgreSQL Parse JSON操作符和函数，它们允许你解析你的JSON文件，并使你的人比以前更容易理解它们。

目录

什么是PostgreSQL？

PostgreSQL Logo: PostgreSQL Parse JSON

图片来源。软件工程日报

PostgreSQL是一个关系型数据库管理系统（RDBMS），由PostgreSQL全球开发组开发。它已经使用了20多年，同时支持SQL和JSON的关系型和非关系型查询，以提供灵活性和SQL兼容性。

PostgreSQL从一开始就以其可靠、功能丰富、性能卓越的实用程序而闻名。许多企业依靠PostgreSQL作为他们的主要数据存储/数据仓库，用于在线、移动、地理空间和分析应用。

PostgreSQL可以在所有主要的操作系统上运行，包括Linux、UNIX（AIX、BSD、HP-UX、SGI IRIX、Mac OS X、Solaris、Tru64）和Windows。

PostgreSQL的主要特点

  • 开源。PostgreSQL是一个对象关系数据库管理系统（ORDBMS）。这使得PostgreSQL可以同时提供面向对象和关系型数据库的功能。它是一个免费使用的开源ORDBMS。
  • **突出的用户群。**PostgreSQL的用户包括苹果、思科、Etsy、Facebook、Instagram、Red Hat、Skype、Spotify和雅虎等知名企业，仅此而已。
  • 多版本并发控制。为了管理并发请求，PostgreSQL有一个多版本并发控制，给每个事务一个数据库的 "快照"，允许在不影响其他事务的情况下进行更改。这在很大程度上消除了对读锁的需要，并确保数据库保持ACID原则。
  • 可靠性和标准符合性。PostgreSQL的写前日志使它成为一个容错的数据库。其庞大的开放源码贡献者基础使其拥有一个内置的社区支持网络。PostgreSQL符合ACID标准，并且完全支持外键、连接、视图、触发器和存储过程，可以使用许多不同的语言。
  • 一个支持性的社区。PostgreSQL提供了一个专门的社区，可以随时为你服务。私人的、第三方的支持服务也是可用的。该社区通过PostgreSQL全球开发小组更新PostgreSQL平台。

关于PostgreSQL主从复制的更多信息，你可以访问我们的广泛指南，涵盖广泛的方面。实现PostgreSQL的主从复制。7个简单的步骤。你也可以在这个指南中学习如何使用PostgreSQL命令行来管理你的PostgreSQL数据库。如何使用PostgreSQL命令行来管理数据库？| 轻松搞定。

什么是数据解析？

Data Parsing: PostgreSQL Parse JSON

图片来源。文档库（Docsumo

从本质上讲，解析是将 一个整体划分为较小的逻辑部分，以便更好地检查。在数据产业领域，数据解析是将数据从一种格式转化为另一种格式的过程，通常是转化为一种容易理解的格式。

数据解析可以意味着任何事情，从简单地分解数据到使用自然语言处理（NLP）算法对数据结构及其组织进行全面分析。在某些情况下，数据解析对于将非结构化或不可读的数据转换为结构良好和易读的数据以更好地进行数据分析是至关重要的。

网络开发人员使用数据解析来使计算机代码更简单。同样地，数据分析师也使用SQL数据解析来逐步解析和执行SQL查询，然后显示结果。数据解析也被用于竞争分析，作为股权研究或商业评估的一部分，由投资者和数据分析师与网络刮擦工具一起使用。

你可以建立或购买一个数据解析器，将数据转换成可读数据。通常情况下，大多数数据分析器分三个步骤工作。它们。

  • **第1步：**获得访问或从数据源中提取数据
  • **第2步：**将其加载到解析引擎中
  • **第3步：**处理它以提取有意义的信息

转换之后，这些数据就可以在业务团队和客户之间共享，使你的业务运营变得灵活和可扩展。

在接下来的章节中，我们将讨论PostgreSQL解析JSON，这是数据解析的众多方式之一，可以将JSON文件转换成可访问的格式。

使用Hevo的无代码数据管道在几分钟内复制PostgreSQL数据

Hevo数据，一个完全管理的数据管道平台，可以帮助你在几次点击中自动化，简化和丰富你的数据复制过程。通过Hevo的各种连接器和快速的数据管道，你可以从100多个数据源中提取和加载数据，如**PostgreSQL（和其他40多个免费来源**）直接进入你的数据仓库或任何数据库。

为了进一步简化和准备你的数据分析，你可以使用Hevo强大的内置转换层来处理和丰富原始颗粒数据，而不需要写一行代码。

免费开始使用 Hevo

Hevo是最快，最简单，最可靠的数据复制平台，将为你的工程带宽和时间节省数倍。今天就试试我们的14天免费试用版，体验一下完全自动化的无忧无虑的PostgreSQL数据复制!

什么是JSON？

JSON File Format: PostgreSQL Parse JSON

图片来源。SQLShack

JSON或JavaScript对象符号是一种开放的标准文件格式（.json），用于结构化、存储和在服务器和Web应用程序之间传输数据。它使用一种简单的格式，以 "键：值"对的形式存储数据，而且人类也很容易理解。事实上，JSON也很容易解析。

下面是一个简单的例子，一个JSON文件包含一个叫做 杂货店有三个对象 类型, 名称和 价格.

{
	"groceries": [

		{
			"type": "fruit",
			"name": "apple",
			"price": 1.36
		},
		{
			"type": "vegetable",
			"name": "lettuce",
			"price": 0.32
		},
		{
			"type": "vegetable",
			"name": "bitter gourd",
			"price": 0.79
		}
	]
}

JSON支持简单和复杂的数据类型，如数字、字符串、布尔值、空、数组、值和对象。

使用JSON最好的部分是它是轻量级的，与语言无关。JSON还内置了对几乎所有一线语言/框架的支持，如C、C++、C#、Java、JavaScript、Perl和Python。

为什么需要PostgreSQL解析JSON？

为了理解PostgreSQL Parse JSON操作的重要性，请考虑一个通常的场景。假设你要求你的销售团队提供最近在你的网站上购买过产品的客户的数据。

他们同意给你这些细节，但你收到的是像这样的意外的东西。

{
"customers": [
{
  	"id": 1,
  	"first_name": "Dayna",
  "last_name": "Wile",
  "gender": "Female",
  "email": "dwile0@facebook.com",
  "phone": "989-311-5262"
}, {
  "id": 2,
  "first_name": "Bliss",
  "last_name": "Pogosian",
  "gender": "Female",
  "email": "bpogosian1@nytimes.com",
  "phone": "466-731-8534"
}, {
  "id": 3,
  "first_name": "Lalo",
  "last_name": "Dener",
  "gender": "Male",
  "email": "ldener2@amazon.co.jp",
  "phone": "656-432-6184"
}, {
  "id": 4,
  "first_name": "Gayleen",
  "last_name": "Mateescu",
  "gender": "Female",
  "email": "gmateescu3@hhs.gov",
  "phone": "281-359-3811"
}, {
  "id": 5,
  "first_name": "Tadd",
  "last_name": "Lotherington",
  "gender": "Male",
  "email": "tlotherington4@nih.gov",
  "phone": "541-923-6204"
}
]
}

显然，这种格式并不适合存储。为了在你的关系型数据库如PostgreSQL中获得这些记录，你需要PostgreSQL解析JSON操作。

PostgreSQL解析JSON操作会把你的JSON键值对，转换成与PostgreSQL的表结构兼容的格式。你可以使用PostgreSQL的JSON运算符和函数来解析JSON对象，然后将这些值插入到你想要的PostgreSQL表中，以供将来使用。

接下来，我们讨论执行解析JSON Postgres操作的过程。

PostgreSQL JSON解析是如何工作的？

自从PostgreSQL 9.2发布以来，有几个重要的改进，包括对JSON数据类型的支持。现在PostgreSQL为用户提供了各种函数和操作符来处理JSON数据。

PostgreSQL解析JSON运算符

下面列出了一些在PostgreSQL中可用的常用的JSON运算符。

操作符说明例子
json -> integer → json提取JSON数组的第n个元素（数组元素从0开始索引，负整数从末尾开始计算）。'[{ "id":1, "first_name":"Walther", "last_name":"Laybourn"}, { "id":2, "first_name":"Calley", "last_name":"Bamblett"}, { "id":3, "first_name":"Cecelia", "last_name":"Asquez"}]':json -> 2 -> { "id":2, "first_name":"Calley", "last_name":"Bamblett"}
json ->text → json提取具有给定键的JSON对象字段'{"姓名":{"Calley": "Bamblett"}}':json -> 'Name' → {"Calley": "Bamblett"}
json->>整数→文本提取JSON数组的第n个元素，作为文本'[10,20,30]'::json -> 2 → 30
json ->> text → text提取具有给定键的JSON对象字段，作为文本'{"Walther":1, "Laybourn":2}':json -> 'Laybourn' → 2
json #> text[] → json在指定的路径提取JSON子对象，其中路径元素可以是字段键或数组索引'{"客户":{"FullName":["Walther", "Laybourn"]}}':json #> '{Customer,FullName,1}' → "Laybourn"
json #>> text[] → text在指定的路径上提取JSON子对象作为文本'{"客户":{"FullName":["Walther", "Laybourn"]}}':json #>> '{Customer,FullName,1}' → Laybourn

是什么让Hevo的ETL流程成为同类中最好的？

如果你有大量的数据，提供一个高质量的ETL解决方案可能是一个困难的任务。Hevo的 自动化，无代码平台使你拥有顺利的数据复制经验所需的一切。

看看是什么让Hevo如此神奇。

  • 完全管理。Hevo不需要管理和维护，因为它是一个完全自动化的平台。
  • 数据转换。Hevo提供了一个简单的界面来完善、修改和充实你要传输的数据。
  • 更快的洞察力生成。Hevo提供近乎实时的数据复制，所以你可以获得实时的洞察力生成和更快的决策。
  • 模式管理。Hevo可以自动检测传入数据的模式，并将其映射到目标模式。
  • 可扩展的基础设施。Hevo有内置的100多个数据源的集成，如**PostgreSQL（有40多个免费的数据源**），可以帮助你根据需要扩展你的数据基础设施。
  • 实时支持。Hevo团队24小时不间断地通过聊天、电子邮件和支持电话向客户提供特殊支持。

在此注册获得14天的免费试用!

关键的PostgreSQL解析JSON函数

下表显示了可在PostgreSQL中使用的JSON函数。

函数描述例子
to_json ( anyelement ) → json将SQL值转换为JSON。如果提供任何SQL数组或组合，将被转换为JSON数组和对象。to_json('Walther Laybourn says "Hi."':text) → "Walther Laybourn says "Hi.""
array_to_json ( anyarray [, boolean ] ) → json将一个SQL数组转换为一个JSON数组array_to_json('{{22,24},{87,89}}':int[]) → [[22,24],87,89]] 。
row_to_json ( record [, boolean ] ) → json将一个SQL复合值转换为一个JSON对象row_to_json(row(1,'Walther'))→ {"f1":1, "f2": "Walther" }
json_object ( text[] ) → json从一个文本数组中创建一个JSON对象。json_object('{"FirstName", "Walther", "SecondName", "Laybourn"}') → {"FirstName" : "Walther", "SecondName" : "Laybourn"}
json_object ( keys text[], values text[] ) → json这个JSON函数从不同的文本数组中成对地获取键和值。json_object('{FirstName,Walther}', '{SecondName,Laybourn}' ) → {"FirstName":"Walther", "SecondName":"Laybourn"}
json_array_elements ( json ) → json的集合将顶层的JSON数组扩展为一个较小的JSON值集合select * from json_array_elements('[customers,1, [FirstName, Walther]]' ) →
value---- customers 1 [FirstName,Walther]
json_array_length ( json ) → 整数返回JSON数组中元素的数量，为整数。json_array_length(‘[1,2,3,{“s1″:1,”s2”:[5,6]},{s3:[6,7,8]},4]’) → 6
json_each ( json ) → setof record ( key text, value json )将顶层的JSON对象扩展为一组键/值对。select * from json_each('{"FirstName: "Walther", "LastName": "Laybourn"}') →
key | value-----+--- FirstName | "Walther" LastName | "Laybourn"
json_object_keys ( json ) → setof text返回顶层JSON对象中的键的集合。select * from json_object_keys('{"p1″: "oranges", "p2″:{"p3″: "apples"}') →
json_object_keys ------ p1 p2

PostgreSQL支持比这里列出的更多的JSON函数和运算符。你可以在下面的PostgreSQL文档中找到更多关于这些的信息 -PostgreSQL JSON函数和运算符。

运行中的PostgreSQL解析JSON操作

既然你现在有了所有关于经常使用的PostgreSQL解析JSON函数和操作符的知识，让我们来解开我们之前的问题，看看我们如何解析从销售团队收到的客户的JSON文件。

1) 插入PostgreSQL JSON数据

收到客户的JSON文件后，你可以使用INSERT函数将JSON数据插入到你的PostgreSQL表中。

下面是这样做的命令。

INSERT INTO
  customers (customer_json_info)
VALUES(
    '{ "id": 1, "first_name": "Dayna", "last_name": "Wile", "gender": "Female", "email", "dwile0@facebook.com", "phone": "989-311-5262"}'
  ),
  (
    '{ "id": 2, "first_name": "Bliss", "last_name": "Pogosian", "gender": "Female", "email": "bpogosian1@nytimes.com", "phone": "466-731-8534"}'
  ),
  (
    '{ "id": 3, "first_name": "Lalo", "last_name": "Dener", "gender": "Male", "email": "ldener2@amazon.co.jp", "phone": "656-432-6184"}'
  ),
  (
    '{ "id": 4, "first_name": "Gayleen", "last_name": "Mateescu", "gender": "Female", "email": "gmateescu3@hhs.gov", "phone": "281-359-3811"}'
  ),
  (
    '{ "id": 5, "first_name": "Tadd", "last_name": "Lotherington", "gender": "Male", "email": "tlotherington4@nih.gov", "phone": "541-923-6204"}'
  );

一旦它进入数据库，你的PostgreSQL的customer_json_info列会看起来像这样。

customer_json_info
{ "id":1, "first_name":"Dayna", "last_name":"Wile", "性别":"女性", "电子邮件", "dwile0@facebook.com", "电话":"989-311-5262"}
{ "id":2, "first_name":"Bliss", "last_name":"Pogosian", "性别":"Female", "email":"bpogosian1@nytimes.com", "电话":"466-731-8534"}
{ "id":3, "first_name":"Lalo", "last_name":"Dener", "性别":"Male", "email":"ldener2@amazon.co.jp", "电话":"656-432-6184"}
{ "id":4, "first_name":"Gayleen", "last_name":"Mateescu", "性别":"Female", "email":"gmateescu3@hhs.gov", "电话":"281-359-3811"}
'{ "id":5, "first_name":"Tadd", "last_name":"Lotherington", "性别":"男性", "电子邮件":"tlotherington4@nih.gov", "电话":"541-923-6204"}

2)查询PostgreSQL JSON数据

要查询数据，你可以像其他SQL查询一样使用SELECT语句。你可以使用本地的PostgreSQL运算符来查询PostgreSQL中的数据。

  • 操作符 -> 按键返回一个JSON对象字段。
  • 操作符 ->> 通过文本返回一个JSON对象字段。

作为一个例子，如果你运行一个SELECT查询。

SELECT customer_json_info ->> CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

你将收到以下输出。

Full_name
Dayna Wile
Bliss Pogosian
拉罗-德纳
盖琳-马泰斯库
Tadd Lotherington

你也可以运行另一个SELECT查询，将顶层的JSON对象扩展为一组键/值对。

让我们考虑一个简单的表格，其中有我们的头两个客户；Dayna Wile从我们的在线商店购买了一件T恤，Bliss Pogosian购买了裤子。

---------------------------------------------------------------------
| id | first_name | last_name | product_description                 |                  
----- ------------ ----------- ---------------------------------------
| 1  | Dayna      | Wile      | {"category":"apparel","product":tshirt"}                    |
----------------------------------------------------------------------
| 2  | Bliss      | Pogosian  | {"category":"accessories","product":"cap"}                  |
----------------------------------------------------------------------

你可以使用PostgreSQL的解析JSON函数返回一组带有键和值列名的行。 json_each_textPostgreSQL解析JSON函数。

SELECT * FROM customers, 
json_each_text(customers.product_description) AS json_data
WHERE VALUE = 'apparel';

输出。

| id | first_name | last_name | product_description| key | value |    
 ---- ------------ ----------- -------------------- ----- ------- 
| 1  | Dayna      | Wil       | {"category":"apparel","product":"tshirt"} | category | apparel | -------------------------------------------------------------------

3) Postgres JSON解析操作

这里有另一个简单的例子来解析PostgreSQL JSON字段。你可以写一个SQL查询来输出一个所有customer_note键的数组。

CREATE TABLE customers (
    id serial primary key,
    customers_json_2 json
);

INSERT INTO customers (customers_json_2) VALUES (
  '{"customer_note": {"note_199": {"message_number": 1453197190, "status": 1}, "note_215": {"message_number": 1459325611, "status": 1}, "note_219": {"message_number": 1454244074, "status": 1}, "note_225": {"message_number": 1453981312, "status": 1}, "note_229": {"date": 1459327685, "status": 1}}}'::json
);

当你运行SELECT命令时，带有 json_object_keysPostgreSQL解析JSON操作。

SELECT json_object_keys(customers_json_2->'customer_note') FROM customers

你会得到下面的输出。

json_object_keys
note_199
note_215
note_219
note_225
note_229

下面是另一个查询，用来返回顶层JSON值的类型。

SELECT json_typeof('{ "id": 1, "first_name": "Dayna", "last_name": "Wile", "gender": "Female", "email", "dwile0@facebook.com", "phone": "989-311-5262"}');

 json_typeof
---------------
 object
(1 row)

有许多更有趣的函数对JSON进行操作，如 jsonb_path_query, json_to_record，以及 jsonb_insert 可以用来执行解析JSON的Postgre操作。在本指南中，我们着重于一些基本的例子，但同样的基本概念允许你在你的PostgreSQL表中解析复杂的JSON文件。

结论

通过使用不同的PostgreSQL解析JSON操作符和函数，PostgreSQL提供了许多方法来执行解析JSON PostgreSQL操作。在本指南中，我们讨论了一些精选的函数，以帮助你了解如何解析或破解JSON对象，从而更好地理解你的PostgreSQL表中的数据。

如果你是一个PostgreSQL用户，使用ETL for Data Analysis将数据复制到仓库中会变得很苛刻。对于初学者来说，这个问题被夸大了，因为他们需要大量的资金和资源来雇佣数据工程师和分析师来理解这些数据。

幸运的是，你可以在几分钟内 使用 Hevo设置并开始从PostgreSQL到你喜欢的仓库的数据复制。甚至非数据专业人员也可以设置和运行他们的PostgreSQL数据管道，而不需要任何广泛的培训或技术资源。

Hevo数据是一个有效和简单的解决方案，用于复制和整合来自异质数据源的数据到一个仓库，因此允许中小型企业顺利运行他们的日常应用。

Hevo Data与100多个数据源的强大集成，如 PostgreSQL，MySQL和MS SQL Server，让你不仅可以从数据源导出数据和加载数据到目的地，而且还可以转换和丰富你的数据，并使其准备好分析，这样你就可以只关注你的关键业务需求，并使用BI工具进行有洞察力的分析。

访问我们的网站来探索Hevo

Hevo可以让你在几分钟内将你的数据从PostgreSQL数据库复制到你选择的任何数据仓库，如Amazon Redshift, Snowflake, Google BigQuery, 或 Firebolt，只需点击几下。

给Hevo一个尝试。在这里注册一个为期14天的完整功能的试用，亲身体验功能丰富的Hevo套件。你也可以查看我们的价格，并决定你最适合的计划。

在下面的评论区分享你对学习PostgreSQL Parse JSON操作的想法。如果你有任何问题，请让我们知道。我们会很乐意帮助你。

用于PostgreSQL的无代码数据管线

免费试用

avatar
文章
阅读
粉丝