CODEWITHSUNDEEP
sqljsonpostgresqlsql-insertjsonb
Ms workaholic
Ms workaholic

Reputation: 393

postgresql insert a structured data into jsonb

I use PostgreSQL 10.11 and would want to enter the following structure into a jsonb field:

{
 lead: {
    name: string,
    prep: boolean
 },
 secondary: {
   {
       name: string,
        prep: boolean
    },
    {
        name: string,
        prep: boolean
    }
}

so lead is an object with name and prep and secondary is an array of name and preps. How can I do that? The scripts below is to create a table with jsonb field:

CREATE TABLE public.test01 (
  name JSONB DEFAULT '{}'::jsonb NOT NULL
) 
WITH (oids = false);

ALTER TABLE public.test01
  ALTER COLUMN id SET STATISTICS 0;

COMMENT ON COLUMN public.test01.name
IS '''[]''';


ALTER TABLE public.test01
  OWNER TO postgres;

I'm trying this insert but get error:

INSERT INTO 
  public.test01
(
  name
  
)
VALUES 
('  
    {"lead": 
        "name": "Paint house", 
        "prep": "yes"}
     , 
     
    "Secondary":
        "name": "John", 
        "prep", "No"}
    }
');

It's the first time I'm using jsonb so a select example would also be helpful to know hoe to read the data as well.

Upvotes: 8

Views: 15217

Answers (1)

GMB
GMB

Reputation: 222462

Your JSON is malformed. Presumably, you meant:

INSERT INTO public.test01 (name)
VALUES (
'{
    "lead": { 
        "name": "Paint house", 
        "prep": "yes"
    }, 
    "Secondary": {
        "name": "John", 
        "prep": "No"
    }
}'::jsonb);

Demo on DB Fiddle

Upvotes: 12

Related Questions