如何在PostgreSQL中定义和查询json列？

在PostgreSQL中定义JSON列的能力使其功能非常强大，并且可以帮助PostgreSQL用户体验SQL和NoSQL的两全其美。

创建JSON列非常简单。您只需要像创建其他列一样创建/定义它，然后使用数据类型作为JSON。

让我们在PostgreSQL中创建一个名为json_test的新表-

CREATE TABLE json_test(
   serial_no SERIAL PRIMARY KEY,
   name VARCHAR,
   metadata JSON
);

现在，让我们用一些数据填充它-

INSERT INTO json_test(name, metadata)
VALUES ('Yash','{"marks_scored":{"science":50,"maths":65}}'),
('Isha', '{"marks_scored":{"science":70,"maths":45}}');

如您所见，JSON值添加在单引号内，就像我们添加VARCHAR / TEXT值一样。

现在，如果查询表（SELECT * from json_test），您将看到以下输出-

但是，我们可以做得更好。假设我想知道Yash和Isha在科学上的得分。我需要做的就是使用->运算符。请参阅下面的示例-

SELECT name, metadata->'marks_scored'->'science' as science_marks
from json_test

输出将是

请注意，在这里，输出列science_marks的类型为JSON，而不是INTEGER。这是因为→运算子总会传回json。除了→运算符外，->>运算符也很常用。两者之间的区别在于，当→返回json时，->>返回文本。

因此，

• 即使我们具有science_marks的整数，元数据→'marks_scored'→'science'仍将返回JSON

• 元数据→'marks_scored'->>'science'将返回文本

• 元数据->>'marks_scored'→'science'将给出错误。由于'marks_scored'输出不再是JSON，因此→运算符对它不起作用。

如果您明确希望采用整数格式的science_marks，则首先要以文本格式获取结果，然后将其转换为整数，如下所示-

SELECT name, CAST(metadata->'marks_scored'->>'science' as integer)
as science_marks from json_test

请注意，您不能将JSON转换为整数。您需要在最后一步使用->>运算符来获取文本输出，然后才可以将文本转换为整数。

就像可以在查询的选择部分中使用JSON列一样，也可以在查询的WHERE部分中使用它们。如果我们希望找出在科学方面得分超过60分的学生，您的查询将像

SELECT name from json_test
WHERE CAST(metadata->'marks_scored'->>'science' as integer) > 60

输出将是