How to insert nested JSON into PostgreSQL-CodePudding

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: The response is always null. Is there any other trick to insert nested JSON into the table?

CodePudding user response：

A quick and dirty example:

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}]

A little better version using the JSON path language from her JSON functions9.16.2. The SQL/JSON Path Language:

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}]

Though the above does depend on the ordering in the Address object staying the same.

CodePudding user response：

CREATE TABLE Log (
    "Name" TEXT,
    "Age"  TEXT,
    "Country" TEXT,
    "Numbers" TEXT
);
insert into Log values('Oslo', '12', 'No', '12');
SELECT json_build_object('Name', 'qazwsx','HostName', "Age", 'Address',
json_build_object(
'Country', 'MNB', 'Numbers', json_build_object('Cell', 7418520)))
FROM Log;

Output:

{"Name" : "qazwsx", "HostName" : "12", "Address" : {"Country" : "MNB", "Numbers" : {"Cell" : 7418520}}}