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
– sticky bit

2020-03-16 22:40:23 +00:00
Commented Mar 16, 2020 at 22:40 — sticky bit
– sticky bit, Commented Mar 16, 2020 at 22:40

GMB · Accepted Answer · 2020-03-16 22:43:30Z

13

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, 2020 at 22:43

answered Mar 16, 2020 at 22:40

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ms workaholic Over a year ago

the secondary part should be an array of name and preps. However, I get error when trying the above query.

GMB Over a year ago

@Msworkaholic: there was another problem in the JSON that I fixed too. I also added a DB Fiddle to my answer, for your reference.

Ms workaholic Over a year ago

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 Over a year ago

Can you give an example of select as well?

GMB Over a year ago

@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

Collectives™ on Stack Overflow

postgresql insert a structured data into jsonb

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related