如何在PostgreSQL中插入嵌套JSON
如何在PostgreSQL中插入嵌套JSON
提问于 2022-08-17 22:07:31
CREATE TABLE Log (
"Name" TEXT,
"Age" TEXT,
"Country" TEXT,
"Numbers" TEXT
);
SELECT "Country", "Numbers"
FROM json_populate_record( null:: log,
'{
"Name": "qazwsx",
"HostName": "Age",
"Address": {
"Country": "MNB",
"Numbers": [
{
"Cell": 7418520
}
]
}
}');
SELECT * FROM Log演示：响应始终为空。还有其他方法可以将嵌套的JSON插入到表中吗？
回答 3
Stack Overflow用户
发布于 2022-08-18 06:32:35
CREATE TABLE Log (
"Name" TEXT,
"Age" TEXT,
"Country" TEXT,
"Numbers" TEXT
);
INSERT INTO Log VALUES('Oslo', '12', 'No', '12');
SELECT jsonb_pretty(
json_build_object(
'Name',
'qazwsx',
'HostName',
"Age",
'Address',
json_build_object(
'Country',
'MNB',
'Numbers',
json_build_object('Cell',7418520)
)
)::jsonb
) AS Output
FROM Log;输出：
{
"Name": "qazwsx",
"HostName": "12",
"Address": {
"Country": "MNB",
"Numbers": {
"Cell": 7418520
}
}
}Stack Overflow用户
发布于 2022-08-17 22:29:31
一个快速而肮脏的例子：
SELECT
json_extract_path_text('{"Name": "qazwsx","HostName": "Age","Address": {
"Country": "MNB", "Numbers": [{"Cell":7418520}]}}'::json, 'Address', 'Country') AS "Country",
json_extract_path_text('{"Name": "qazwsx","HostName": "Age","Address": {
"Country": "MNB", "Numbers": [{"Cell":7418520}]}}'::json, 'Address', 'Numbers') AS "Numbers";
Country | Numbers
---------+--------------------
"MNB" | [{"Cell":7418520}]使用JSON函数9.16.2中的JSON语言的更好版本。SQL/JSON路径语言
SELECT
t[0] AS "Country",
t[1] AS "Numbers"
FROM
jsonb_path_query_array('{"Name": "qazwsx","HostName": "Age","Address": {
"Country": "MNB", "Numbers": [{"Cell":7418520}]}}'::jsonb, '$.Address.*') AS t;
Country | Numbers
---------+---------------------
"MNB" | [{"Cell": 7418520}]尽管上述情况确实取决于Address对象中保持不变的顺序。
Stack Overflow用户
发布于 2022-08-19 07:03:34
CREATE TABLE Log (
"Name" TEXT,
"Age" TEXT,
"Country" TEXT,
"Numbers" TEXT
);
DECLARE
jsonstr text;
jsonobj jsonb;
jsonstr = '{
"Name": "qazwsx",
"HostName": "Age",
"Address": {
"Country": "MNB",
"Numbers": [
{
"Cell": 7418520
}
]
}
}';
SELECT jsonstr::json INTO jsonobj;
INSERT INTO
Log
SELECT
jsonobj->'Name',
jsonobj->'HostName'
((jsonobj->'Address')::json)->'Country',
(((jsonobj->'Address')::json)#>'{Numbers,0}')::json->'Cell';
SELECT * FROM Log页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73395407
复制相关文章
相似问题
腾讯云开发者
