【PostgreSQL】SQL还是NoSQL？为什么不同时使用（与PostgreSQL一起使用）？

对于任何一个开始新项目的开发人员来说，这都是一个艰难的决定。您应该将数据存储在标准的、经过时间测试的SQL数据库中，还是使用较新的基于NoSQL文档的数据库？这个看似简单的决定实际上可以决定你的项目的成败。正确选择并构建好数据结构，你就可以顺利进入生产阶段，看着你的应用程序起飞。如果选择错误，你可能会在应用程序问世之前做噩梦（甚至可能会进行一些重大重写）。

简洁与强大

SQL和NoSQL解决方案都存在折衷。通常，开始使用NoSQL数据结构更容易，尤其是在数据复杂或层次结构时。您只需从前端代码中获取一个JSON数据对象，并将其放入数据库中即可完成操作。但以后，当您需要访问该数据来回答一些基本的业务问题时，这将更加困难。SQL解决方案使收集数据和得出结论变得更加容易。让我们看一个例子：

每天我都会跟踪我吃的食物，以及每种食物中的卡路里数：

Day	Food Item	Calories	Meal
01 Jan	Apple	72	Breakfast
01 Jan	Oatmeal	146	Breakfast
01 Jan	Sandwich	445	Lunch
01 Jan	Chips	280	Lunch
01 Jan	Cookie	108	Lunch
01 Jan	Mixed Nuts	175	Snack
01 Jan	Pasta/Sauce	380	Dinner
01 Jan	Garlic Bread	200	Dinner
01 Jan	Broccoli	32	Dinner

我还跟踪我喝的水的杯数以及何时喝：

Day	Time	Cups
Jan 01	08:15	1
Jan 01	09:31	1
Jan 01	10:42	2
Jan 01	12:07	2
Jan 01	14:58	1
Jan 01	17:15	1
Jan 01	18:40	1
Jan 01	19:05	1

最后，我跟踪我的锻炼：

Day	Time	Duration	Exercise
Jan 01	11:02	0.5	Walking
Jan 02	09:44	0.75	Bicycling
Jan 02	17:00	0.25	Walking

对于每一天，我还跟踪我当前的体重以及当天的任何笔记：

Day	Weight	Notes
Jan 01	172.6	This new diet is awesome!
Jan 14	170.2	Not sure all this is worth it.
Jan 22	169.8	Jogged past a McDonald's today. It was hard.
Feb 01	168.0	I feel better, but sure miss all that greasy food.

收集所有数据

需要收集、存储、检索和稍后分析大量不同的数据。它的组织简单而容易，但记录的数量每天都有所不同。在任何一天，我可能没有或更多关于食物、水和锻炼的条目，我可能有零或一个关于体重和笔记的条目。

在我的应用程序中，我在一个页面上收集一天的所有数据，让我的用户更轻松。因此，我每天都会得到一个JSON对象，如下所示：

{
  "date": "2022-01-01",
  "weight": 172.6,
  "notes": "This new diet is awesome!",
  "food": [
    { "title": "Apple", "calories": 72, "meal": "Breakfast" },
    { "title": "Oatmeal", "calories": 146, "meal": "Breakfast" },
    { "title": "Sandwich", "calories": 445, "meal": "Lunch" },
    { "title": "Chips", "calories": 280, "meal": "Lunch" },
    { "title": "Cookie", "calories": 108, "meal": "Lunch" },
    { "title": "Mixed Nuts", "calories": 175, "meal": "Snack" },
    { "title": "Pasta/Sauce", "calories": 380, "meal": "Dinner" },
    { "title": "Garlic Bread", "calories": 200, "meal": "Dinner" },
    { "title": "Broccoli", "calories": 32, "meal": "Dinner" }
  ],
  "water": [
    { "time": "08:15", "qty": 1 },
    { "time": "09:31", "qty": 1 },
    { "time": "10:42", "qty": 2 },
    { "time": "10:42", "qty": 2 },
    { "time": "12:07", "qty": 1 },
    { "time": "14:58", "qty": 1 },
    { "time": "17:15", "qty": 1 },
    { "time": "18:40", "qty": 1 },
    { "time": "19:05", "qty": 1 }
  ],
  "exercise": [{ "time": "11:02", "duration": 0.5, "type": "Walking" }]
}

保存数据

一旦我们收集了一天的所有数据，我们就需要将其存储在数据库中。在NoSQL数据库中，这可能是一个非常简单的过程，因为我们只需为特定日期的特定用户创建一个记录（文档），然后将文档放入集合中，就完成了。使用SQL，我们有一些必须在其中工作的结构，在这种情况下，它看起来像4个独立的表：食物、水、锻炼和笔记。我们想在这里做4个单独的插入，每个表一个。如果我们没有特定表格的数据（比如今天没有记录锻炼），那么我们可以跳过该表格。

如果您使用SQL来存储这些数据，您可能希望在数据输入表单中输入每个表的数据时保存该表的数据（而不是等到输入完所有数据后再保存。）或者您可能希望创建一个数据库函数，该函数获取所有JSON数据，对其进行解析，并在单个事务中将其写入所有相关表。有很多方法可以处理这一问题，但只要这样说就足够了：这比将数据保存在NoSQL数据库中要复杂一些。

检索数据

如果我们想显示一天的所有数据，那基本上是一样的。使用NoSQL，您可以获取用户当天的数据，然后在应用程序中使用它。美好的使用SQL，我们需要查询4个表来获取所有数据（或者我们可以使用一个函数在一次调用中获取所有数据。

分析数据

现在我们已经保存了数据，并且可以检索和显示它，让我们使用它进行一些分析。让我们展示一张我在过去一个月里总共摄入了多少卡路里的图表。使用SQL，这是一项简单的任务：

select
  date,
  sum(calories) as total_calories
from food_log
group by date
where user_id = 'xyz' and day between '2022-01-01' and '2022-01-31'
order by date;

砰！完成！现在我们可以把这些结果发送到我们的绘图库，并把我的饮食习惯画得很漂亮。

但是，如果我们将这些数据存储在NoSQL中，它会变得更加复杂。我们需要：

• 为用户获取当月的所有数据
• 解析每天的数据以获得食物日志信息
• 每天循环并计算热量
• 将聚合数据发送到我们的绘图模块

如果这是我们要定期做的事情，那么计算每天的总热量并将其存储在当天的文档中是有意义的，这样我们就可以更快地获取数据。但这需要更多的前期工作，我们仍然需要提取每天的数据，并首先解析出卡路里总量。如果我们更新数据，我们仍然需要重新计算并更新总数。最终，我们会想在水和运动总量方面做到这一点。代码最终会变得越来越长、越来越复杂。

SQL和NoSQL在一起-FTW

让我们看看如何在同一个数据库中使用SQL的强大功能和NoSQL的易用性，使这一切变得更容易。我们将为每天的数据（为每个用户）创建一个表，并首先存储权重和注释等基本字段。然后，我们将在JSONB字段中抛出food_log、water_log和exercise_log字段。

CREATE TABLE calendar (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    date date,
    user_id uuid NOT NULL,
    weight numeric,
    notes text,
    food_log jsonb,
    water_log jsonb,
    exercise_log jsonb
);
-- (Optional) - create a foreign key relationship for the user_id field
ALTER TABLE ONLY calendar
    ADD CONSTRAINT calendar_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);

现在让我们在表中插入一些数据。PostgreSQL同时提供JSON和JSONB字段，由于后者更容易被数据库优化，查询处理速度更快，所以我们几乎总是希望使用JSONB。我们将使用food_log、water_log和exercise_log的JSONB字段，并将从应用程序中获得的数据作为字符串直接转储到这些字段中：

insert into calendar
  (date, user_id, weight, notes, food_log, water_log, exercise_log)
values
  (
    '2022-01-01',
    'xyz',
    172.6,
    'This new diet is awesome!',
    '[
      { "title": "Apple", "calories": 72, "meal": "Breakfast"},
      { "title": "Oatmeal", "calories": 146, "meal": "Breakfast"},
      { "title": "Sandwich", "calories": 445, "meal": "Lunch"},
      { "title": "Chips", "calories": 280, "meal": "Lunch"},
      { "title": "Cookie", "calories": 108, "meal": "Lunch"},
      { "title": "Mixed Nuts", "calories": 175, "meal": "Snack"},
      { "title": "Pasta/Sauce", "calories": 380, "meal": "Dinner"},
      { "title": "Garlic Bread", "calories": 200, "meal": "Dinner"},
      { "title": "Broccoli", "calories": 32, "meal": "Dinner"}
     ]',
    '[
      {"time": "08:15", "qty": 1},
      {"time": "09:31", "qty": 1},
      {"time": "10:42", "qty": 2},
      {"time": "10:42", "qty": 2},
      {"time": "12:07", "qty": 1},
      {"time": "14:58", "qty": 1},
      {"time": "17:15", "qty": 1},
      {"time": "18:40", "qty": 1},
      {"time": "19:05", "qty": 1}
    ]',
    '[
      {"time": "11:02", "duration": 0.5, "type": "Walking"}
    ]'
  );

虽然这是一个很大的insert语句，但它肯定比在4个单独的表上进行insert要好。对于所有这些食物条目和水日志条目，我们必须在主表中创建1个条目，然后是9个食物日志条目、9个水日志条目和一个锻炼日志条目，总共20个数据库记录。我们把它包装成一张唱片。

但是我们如何查询这些数据？

很好，我们现在正在收集数据，而且很容易将数据插入数据库。编辑数据也不算太糟，因为我们只是将数据下载到客户端，根据需要更新JSON字段，然后将它们扔回数据库。不太难。但我如何查询这些数据？以前的任务怎么样？让我们展示一张我在过去一个月里总共摄入了多少卡路里的图表。

在这种情况下，该数据存储在日历表的food_log字段中。如果PostgreSQL能够将JSONB数组转换为单独的数据库记录（记录集）就好了。的确如此！jsonb_array_elements函数将为我们做这件事，允许创建一个简单的表，我们可以使用它来计算我们的热量摄入。

以下是一些SQL，用于将food_log数组转换为单独的输出记录：

select
  user_id,
  date,
  jsonb_array_elements(food_log)->>'title' as title,
  jsonb_array_elements(food_log)->'calories' as calories,
  jsonb_array_elements(food_log)->'meal' as meal
from calendar
where user_id = 'xyz'
  and date between '2022-01-01' and '2022-01-31';

这将返回一个如下所示的表：

date	title	calories	meal
2022-01-01	Apple	72	Breakfast
2022-01-01	Oatmeal	146	Breakfast
2022-01-01	Sandwich	445	Lunch
2022-01-01	Chips	280	Lunch
2022-01-01	Cookie	108	Lunch
2022-01-01	Mixed Nuts	175	Snack
2022-01-01	Pasta/Sauce	380	Dinner
2022-01-01	Garlic Bread	200	Dinner
2022-01-01	Broccoli	32	Dinner

需要注意的几点：

• jsonb_array_elements(food_log)->>'title' as title 作为title这将返回一个文本字段，因为->>运算符返回text
• jsonb_array_elements(food_log)->'calories' as calories ，这返回一个JSON对象，因为->运算符返回JSON

如果我们想对卡路里求和以获得一些总数，我们就不能有JSON对象，所以我们需要将其转换为更有用的对象，比如INTEGER：

• (jsonb_array_elements(food_log)->'calories')::INTEGER as calories，返回一个整数

现在我们不能只在这个问题上加上求和运算来得到一天的总热量。如果我们尝试这样做：

select
  date,
  sum((jsonb_array_elements(food_log)->'calories')::integer) as total_calories
from calendar where user_id = 'xyz'
  and date between '2022-01-01' and '2022-01-31'
group by date;

我们从PostgreSQL得到一个错误：运行sql查询失败：聚合函数调用不能包含返回集合的函数调用。

相反，我们需要将其视为一组构建块，其中我们的第一条SQL语句返回一个表：

select
  date,
  (jsonb_array_elements(food_log)->'calories')::integer as calories
from calendar where user_id = 'xyz'
  and date between '2022-01-01' and '2022-01-31';

现在，我们可以使用“table”语句，在它周围插入一些（括号），然后查询它：

with data as
(
  select
    date,
    (jsonb_array_elements(food_log)->'calories')::integer as calories
  from calendar
  where user_id = 'xyz'
    and date between '2022-01-01' and '2022-01-31'
)
select date, sum(calories)
from data
group by date;

这正是我们想要的：

date	sum
2022-01-01	1838

如果我们在这个月剩下的日子里添加更多的数据，我们将拥有一个漂亮的图表所需的所有数据。

搜索数据

如果我们想回答这个问题：我上个月吃的大蒜面包里有多少卡路里？这些数据存储在日历表的food_log字段中。我们可以使用以前使用的相同类型的查询来“压平”food_log数据，以便进行搜索。

要获得我在一月份吃的每一件东西，我们可以使用：

select
  date,
  jsonb_array_elements(food_log)->>'title' as title,
  (jsonb_array_elements(food_log)->'calories')::integer as calories
from calendar
where user_id = 'xyz'
  and date between '2022-01-01' and '2022-01-31'

现在，为了搜索大蒜面包，我们可以在它周围放（括号）来制作一张“桌子”，然后搜索我们想要的物品：

with my_food as
(
  select
    date,
    jsonb_array_elements(food_log)->>'title' as title,
    (jsonb_array_elements(food_log)->'calories')::integer as calories
  from calendar
  where user_id = 'xyz'
    and date between '2022-01-01' and '2022-01-31'
)
select
  title,
  calories
from my_food
where title = 'Garlic Bread';

这给了我们：

title	calories
Garlic Bread	200

结论

如果我们花一点时间研究PostgreSQL提供的JSON函数和运算符，我们可以将Postgres变成一个易于使用的NoSQL数据库，它仍然保留了SQL的所有功能。这为我们在数据库中存储来自应用程序代码的复杂JSON数据提供了一种非常简单的方法。然后，我们可以使用强大的SQL功能来分析数据，并在应用程序中显示这些数据。这是两全其美！