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

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

简洁与强大

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

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

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

最后，我跟踪我的锻炼：

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

收集所有数据

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

在我的应用程序中，我在一个页面上收集一天的所有数据，让我的用户更轻松。因此，我每天都会得到一个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';

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

需要注意的几点：

• 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;

这正是我们想要的：

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

搜索数据

如果我们想回答这个问题：我上个月吃的大蒜面包里有多少卡路里？这些数据存储在日历表的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';

这给了我们：

结论

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