How to insert nested JSON into PostgreSQL

Question

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?

Did you mean to have INSERT INTO log before your first SELECT clause? Otherwise, you're not modifying the table. — Tim Roberts
– Tim Roberts, Commented Aug 17, 2022 at 22:17

Barbaros Özhan · Accepted Answer · 2022-08-19 06:45:24Z

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;

Output:

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

Adrian Klaver · Accepted Answer · 2022-08-17 22:40:01Z

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.

maddy23285 · Accepted Answer · 2022-08-19 07:03:34Z

0

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

answered Aug 19, 2022 at 7:03

maddy23285

7867 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to insert nested JSON into PostgreSQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related