postgresql insert a structured data into jsonb

Question

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.

You got three `}` yet only one `{`... – sticky bit Mar 16 '20 at 22:40 — sticky bit, Mar 16 '20 at 22:40

GMB · Accepted Answer · 2020-03-16T22:43:30.113

11

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

edited Mar 16 '20 at 22:43

answered Mar 16 '20 at 22:40

GMB

216,147
25
84
135

the secondary part should be an array of name and preps. However, I get error when trying the above query. – Ms workaholic Mar 16 '20 at 22:42
@Msworkaholic: there was another problem in the JSON that I fixed too. I also added a DB Fiddle to my answer, for your reference. – GMB Mar 16 '20 at 22:44
Thank you It worked for an array with a little change too INSERT INTO public.test01 (name) VALUES ( '{ "lead": { "name": "Paint house", "prep": "yes" }, "Secondary": [{ "name": "John", "prep": "No" }, { "name": "Joe", "prep": "Y" }] }'::jsonb); – Ms workaholic Mar 16 '20 at 22:46
Can you give an example of select as well? – Ms workaholic Mar 16 '20 at 22:46
@Msworkaholic: it is really about your JSON, not about the SQL itself. If you have a valid JSON string, you can just do: `select '{ "lead": { "name": "Paint house", "prep": "yes" }, "Secondary": { "name": "John", "prep": "No" } }'::jsonb myjson` – GMB Mar 16 '20 at 22:53

postgresql insert a structured data into jsonb

1 Answers1