How to delete an object in a json array element?

Question

Let's say I have this json format:

[
    {
       "firstName": "John", 
       "lastName": "Doe"
    }
]

and I want to delete the "lastName", how can I do that?

So far I have this query and it works, but the problem is it adds another array

UPDATE person
SET 
    field = jsonb_set(field::jsonb, 
    '{0}', 
    field::jsonb #- '{0,lastName}',
    false)

The result from my query above:

[
    [
       {
           "firstName": "John"
       }
    ]
]

The result that I want:

[
    {
       "firstName": "John"
    }
]

Simple answer: you can not ! If you want your desired result then you have to copy the thing without the parts you want deleted. You may set them to zero or undefined but they would still be there. — Thomas Ludewig, Oct 25 '19 at 02:02
At least when you run over them with Object.keys(). The Name will remain. — Thomas Ludewig, Oct 25 '19 at 02:03
Just a thought! How about deleting this record and adding a new one? i.e. No editing stuff — sam, Oct 25 '19 at 02:11
You want to delete a record from a json object, not an array. Should fix your question's title. — Shawn, Oct 25 '19 at 02:36

score 1 · Accepted Answer · answered Oct 25 '19 at 02:37

1

Use the first element of the array (not the whole array) in the third parameter:

UPDATE person
SET 
    field = jsonb_set(field::jsonb, 
    '{0}', 
    (field::jsonb->0) - 'lastName',
    false)

answered Oct 25 '19 at 02:37

klin

score 0 · Answer 2 · answered Oct 25 '19 at 15:14

0

The unneeded jsonb_set is causing the problem in this case. Get rid of it.

UPDATE person
SET 
    field = field::jsonb #- '{0,lastName}'

answered Oct 25 '19 at 15:14

jjanes

score -1 · Answer 3 · answered Oct 25 '19 at 02:26

-1

You could try with REGEXP_REPLACE.

The super basic pattern would be something like "lastName":"[a-zA-Z]*".

answered Oct 25 '19 at 02:26

tymtam

score -2 · Answer 4 · answered Oct 25 '19 at 02:04

-2

I can say it is impossible. Please don't try it.

answered Oct 25 '19 at 02:04

Maosen Hu

I did not down vote but comments should stay in comment section since it is not an answer, so wait till you have enough reputation to comment (50). – Ajan Balakumaran Oct 25 '19 at 04:08
In addition to what Ajan wrote: it's not impossible – Oct 25 '19 at 07:09

4 Answers4