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
DEMO：响应始终为null。在表中插入嵌套的JSON还有其他技巧吗？
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
}
}
}
一个快速而肮脏的例子：
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对象中的排序保持不变。
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