CODEWITHSUNDEEP
sqljsonpostgresqljsonb
Chand
Chand

Reputation: 51

How to insert nested JSON into PostgreSQL

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?

Upvotes: 1

Views: 1423

Answers (3)

maddy23285
maddy23285

Reputation: 786

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

Upvotes: 0

Sergey Zaykov
Sergey Zaykov

Reputation: 586

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

Upvotes: 2

Adrian Klaver
Adrian Klaver

Reputation: 19655

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.

Upvotes: 1

Related Questions