How to remove object from json array?
3
My table:
CREATE TABLE items (
id BIGINT PRIMARY KEY NOT NULL,
name VARCHAR,
images json
);
images format:
[
{
"id": "owner",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_0",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_1",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_2",
"full": "<url>",
"thumb": "<url>"
}
]
I need something like this:
UPDATE items SET images = delete(images, 'note_1');
postgresql postgresql-9.3 json
edited Dec 6 '14 at 2:42
Erwin Brandstetter
92.5k9176290
asked Dec 5 '14 at 18:49
TSKTSK
1813
add a comment |
3
My table:
CREATE TABLE items (
id BIGINT PRIMARY KEY NOT NULL,
name VARCHAR,
images json
);
images format:
[
{
"id": "owner",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_0",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_1",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_2",
"full": "<url>",
"thumb": "<url>"
}
]
I need something like this:
UPDATE items SET images = delete(images, 'note_1');
postgresql postgresql-9.3 json
edited Dec 6 '14 at 2:42
Erwin Brandstetter
92.5k9176290
asked Dec 5 '14 at 18:49
TSKTSK
1813
Please be more specific: do you mean'{"id":"note_1"}'or delete any element from the array that has'note_1'as key or value?
– Erwin Brandstetter
Dec 6 '14 at 1:21
@ErwinBrandstetter, delete{"id": "note_1", "full": "<url>", "thumb": "<url>"}where id = "note_1"
– TSK
Dec 8 '14 at 17:26
add a comment |
3
3
3
1
My table:
CREATE TABLE items (
id BIGINT PRIMARY KEY NOT NULL,
name VARCHAR,
images json
);
images format:
[
{
"id": "owner",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_0",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_1",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_2",
"full": "<url>",
"thumb": "<url>"
}
]
I need something like this:
UPDATE items SET images = delete(images, 'note_1');
postgresql postgresql-9.3 json
edited Dec 6 '14 at 2:42
Erwin Brandstetter
92.5k9176290
asked Dec 5 '14 at 18:49
TSKTSK
1813
My table:
CREATE TABLE items (
id BIGINT PRIMARY KEY NOT NULL,
name VARCHAR,
images json
);
images format:
[
{
"id": "owner",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_0",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_1",
"full": "<url>",
"thumb": "<url>"
},
{
"id": "note_2",
"full": "<url>",
"thumb": "<url>"
}
]
I need something like this:
UPDATE items SET images = delete(images, 'note_1');
postgresql postgresql-9.3 json
postgresql postgresql-9.3 json
edited Dec 6 '14 at 2:42
Erwin Brandstetter
92.5k9176290
asked Dec 5 '14 at 18:49
TSKTSK
1813
edited Dec 6 '14 at 2:42
Erwin Brandstetter
92.5k9176290
asked Dec 5 '14 at 18:49
TSKTSK
1813
edited Dec 6 '14 at 2:42
Erwin Brandstetter
92.5k9176290
edited Dec 6 '14 at 2:42
Erwin Brandstetter
92.5k9176290
edited Dec 6 '14 at 2:42
Erwin Brandstetter
92.5k9176290
92.5k9176290
asked Dec 5 '14 at 18:49
TSKTSK
1813
asked Dec 5 '14 at 18:49
TSKTSK
1813
asked Dec 5 '14 at 18:49
TSKTSK
1813
1813
Please be more specific: do you mean'{"id":"note_1"}'or delete any element from the array that has'note_1'as key or value?
– Erwin Brandstetter
Dec 6 '14 at 1:21
@ErwinBrandstetter, delete{"id": "note_1", "full": "<url>", "thumb": "<url>"}where id = "note_1"
– TSK
Dec 8 '14 at 17:26
add a comment |
Please be more specific: do you mean'{"id":"note_1"}'or delete any element from the array that has'note_1'as key or value?
– Erwin Brandstetter
Dec 6 '14 at 1:21
@ErwinBrandstetter, delete{"id": "note_1", "full": "<url>", "thumb": "<url>"}where id = "note_1"
– TSK
Dec 8 '14 at 17:26
Please be more specific: do you mean
'{"id":"note_1"}' or delete any element from the array that has 'note_1' as key or value?– Erwin Brandstetter
Dec 6 '14 at 1:21
Please be more specific: do you mean
'{"id":"note_1"}' or delete any element from the array that has 'note_1' as key or value?– Erwin Brandstetter
Dec 6 '14 at 1:21
@ErwinBrandstetter, delete
{"id": "note_1", "full": "<url>", "thumb": "<url>"} where id = "note_1"– TSK
Dec 8 '14 at 17:26
@ErwinBrandstetter, delete
{"id": "note_1", "full": "<url>", "thumb": "<url>"} where id = "note_1"– TSK
Dec 8 '14 at 17:26
add a comment |
1 Answer
1
active
oldest
votes
3
To remove all elements from the column images (holding a json array) where 'id' is 'note_1':
pg 9.3
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items i2
, json_array_elements(i2.images) elem
WHERE elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id
AND json_array_length(i2.images) < json_array_length(i.images);
SQL Fiddle.
Explain
- Unnest the JSON array with
json_array_elements()in a subquery using an implicitJOIN LATERALfor the set-returning function. Details:
- PostgreSQL joining using JSONB
- How to turn json array into postgres array?
- JOIN to the base table and add another
WHEREcondition usingjson_array_length()to exclude unaffected rows - so you don't update each and every row of the table, which would be expensive (and wide-spread) nonsense.
pg 9.4
This gets much easier with jsonb and additional jsonb operators.
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items cand
, json_array_elements(cand.images) elem
WHERE cand.images @> '{[{"id":"note_1"}]}'::jsonb
AND elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id;Eliminates unaffected rows at the start, which is much faster. Plus, there is extensive native index support for jsonb, too, now.
Here are some example, benchmarks and comparison of new features to old json and MongoDB, plus outlook to jsquery by (some of) their main authors, Alexander Korotkov, Oleg Bartunov andTeodor Sigaevat PGCon 2014:
"CREATE INDEX ... USING VODKA"
edited Apr 13 '17 at 12:42
Community♦
1
answered Dec 6 '14 at 2:09
Erwin BrandstetterErwin Brandstetter
92.5k9176290
what does items cand mean
– CommonSenseCode
Aug 29 '18 at 17:20
@commonSenseCode:FROM items candis short syntax forFROM items AS cand-candis a table alias. TheASkeyword is safe to omit for table aliases (as opposed to column aliases). See: dba.stackexchange.com/a/61455/3684 or stackoverflow.com/a/11543614/939860
– Erwin Brandstetter
Sep 2 '18 at 1:37
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f84472%2fhow-to-remove-object-from-json-array%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
To remove all elements from the column images (holding a json array) where 'id' is 'note_1':
pg 9.3
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items i2
, json_array_elements(i2.images) elem
WHERE elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id
AND json_array_length(i2.images) < json_array_length(i.images);
SQL Fiddle.
Explain
- Unnest the JSON array with
json_array_elements()in a subquery using an implicitJOIN LATERALfor the set-returning function. Details:
- PostgreSQL joining using JSONB
- How to turn json array into postgres array?
- JOIN to the base table and add another
WHEREcondition usingjson_array_length()to exclude unaffected rows - so you don't update each and every row of the table, which would be expensive (and wide-spread) nonsense.
pg 9.4
This gets much easier with jsonb and additional jsonb operators.
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items cand
, json_array_elements(cand.images) elem
WHERE cand.images @> '{[{"id":"note_1"}]}'::jsonb
AND elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id;Eliminates unaffected rows at the start, which is much faster. Plus, there is extensive native index support for jsonb, too, now.
Here are some example, benchmarks and comparison of new features to old json and MongoDB, plus outlook to jsquery by (some of) their main authors, Alexander Korotkov, Oleg Bartunov andTeodor Sigaevat PGCon 2014:
"CREATE INDEX ... USING VODKA"
edited Apr 13 '17 at 12:42
Community♦
1
answered Dec 6 '14 at 2:09
Erwin BrandstetterErwin Brandstetter
92.5k9176290
what does items cand mean
– CommonSenseCode
Aug 29 '18 at 17:20
@commonSenseCode:FROM items candis short syntax forFROM items AS cand-candis a table alias. TheASkeyword is safe to omit for table aliases (as opposed to column aliases). See: dba.stackexchange.com/a/61455/3684 or stackoverflow.com/a/11543614/939860
– Erwin Brandstetter
Sep 2 '18 at 1:37
add a comment |
3
To remove all elements from the column images (holding a json array) where 'id' is 'note_1':
pg 9.3
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items i2
, json_array_elements(i2.images) elem
WHERE elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id
AND json_array_length(i2.images) < json_array_length(i.images);
SQL Fiddle.
Explain
- Unnest the JSON array with
json_array_elements()in a subquery using an implicitJOIN LATERALfor the set-returning function. Details:
- PostgreSQL joining using JSONB
- How to turn json array into postgres array?
- JOIN to the base table and add another
WHEREcondition usingjson_array_length()to exclude unaffected rows - so you don't update each and every row of the table, which would be expensive (and wide-spread) nonsense.
pg 9.4
This gets much easier with jsonb and additional jsonb operators.
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items cand
, json_array_elements(cand.images) elem
WHERE cand.images @> '{[{"id":"note_1"}]}'::jsonb
AND elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id;Eliminates unaffected rows at the start, which is much faster. Plus, there is extensive native index support for jsonb, too, now.
Here are some example, benchmarks and comparison of new features to old json and MongoDB, plus outlook to jsquery by (some of) their main authors, Alexander Korotkov, Oleg Bartunov andTeodor Sigaevat PGCon 2014:
"CREATE INDEX ... USING VODKA"
edited Apr 13 '17 at 12:42
Community♦
1
answered Dec 6 '14 at 2:09
Erwin BrandstetterErwin Brandstetter
92.5k9176290
what does items cand mean
– CommonSenseCode
Aug 29 '18 at 17:20
@commonSenseCode:FROM items candis short syntax forFROM items AS cand-candis a table alias. TheASkeyword is safe to omit for table aliases (as opposed to column aliases). See: dba.stackexchange.com/a/61455/3684 or stackoverflow.com/a/11543614/939860
– Erwin Brandstetter
Sep 2 '18 at 1:37
add a comment |
3
3
3
To remove all elements from the column images (holding a json array) where 'id' is 'note_1':
pg 9.3
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items i2
, json_array_elements(i2.images) elem
WHERE elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id
AND json_array_length(i2.images) < json_array_length(i.images);
SQL Fiddle.
Explain
- Unnest the JSON array with
json_array_elements()in a subquery using an implicitJOIN LATERALfor the set-returning function. Details:
- PostgreSQL joining using JSONB
- How to turn json array into postgres array?
- JOIN to the base table and add another
WHEREcondition usingjson_array_length()to exclude unaffected rows - so you don't update each and every row of the table, which would be expensive (and wide-spread) nonsense.
pg 9.4
This gets much easier with jsonb and additional jsonb operators.
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items cand
, json_array_elements(cand.images) elem
WHERE cand.images @> '{[{"id":"note_1"}]}'::jsonb
AND elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id;Eliminates unaffected rows at the start, which is much faster. Plus, there is extensive native index support for jsonb, too, now.
Here are some example, benchmarks and comparison of new features to old json and MongoDB, plus outlook to jsquery by (some of) their main authors, Alexander Korotkov, Oleg Bartunov andTeodor Sigaevat PGCon 2014:
"CREATE INDEX ... USING VODKA"
edited Apr 13 '17 at 12:42
Community♦
1
answered Dec 6 '14 at 2:09
Erwin BrandstetterErwin Brandstetter
92.5k9176290
To remove all elements from the column images (holding a json array) where 'id' is 'note_1':
pg 9.3
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items i2
, json_array_elements(i2.images) elem
WHERE elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id
AND json_array_length(i2.images) < json_array_length(i.images);
SQL Fiddle.
Explain
- Unnest the JSON array with
json_array_elements()in a subquery using an implicitJOIN LATERALfor the set-returning function. Details:
- PostgreSQL joining using JSONB
- How to turn json array into postgres array?
- JOIN to the base table and add another
WHEREcondition usingjson_array_length()to exclude unaffected rows - so you don't update each and every row of the table, which would be expensive (and wide-spread) nonsense.
pg 9.4
This gets much easier with jsonb and additional jsonb operators.
UPDATE items i
SET images = i2.images
FROM (
SELECT id, array_to_json(array_agg(elem)) AS images
FROM items cand
, json_array_elements(cand.images) elem
WHERE cand.images @> '{[{"id":"note_1"}]}'::jsonb
AND elem->>'id' <> 'note_1'
GROUP BY 1
) i2
WHERE i2.id = i.id;Eliminates unaffected rows at the start, which is much faster. Plus, there is extensive native index support for jsonb, too, now.
Here are some example, benchmarks and comparison of new features to old json and MongoDB, plus outlook to jsquery by (some of) their main authors, Alexander Korotkov, Oleg Bartunov andTeodor Sigaevat PGCon 2014:
"CREATE INDEX ... USING VODKA"
edited Apr 13 '17 at 12:42
Community♦
1
answered Dec 6 '14 at 2:09
Erwin BrandstetterErwin Brandstetter
92.5k9176290
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
1
answered Dec 6 '14 at 2:09
Erwin BrandstetterErwin Brandstetter
92.5k9176290
answered Dec 6 '14 at 2:09
Erwin BrandstetterErwin Brandstetter
92.5k9176290
answered Dec 6 '14 at 2:09
Erwin BrandstetterErwin Brandstetter
92.5k9176290
92.5k9176290
what does items cand mean
– CommonSenseCode
Aug 29 '18 at 17:20
@commonSenseCode:FROM items candis short syntax forFROM items AS cand-candis a table alias. TheASkeyword is safe to omit for table aliases (as opposed to column aliases). See: dba.stackexchange.com/a/61455/3684 or stackoverflow.com/a/11543614/939860
– Erwin Brandstetter
Sep 2 '18 at 1:37
add a comment |
what does items cand mean
– CommonSenseCode
Aug 29 '18 at 17:20
@commonSenseCode:FROM items candis short syntax forFROM items AS cand-candis a table alias. TheASkeyword is safe to omit for table aliases (as opposed to column aliases). See: dba.stackexchange.com/a/61455/3684 or stackoverflow.com/a/11543614/939860
– Erwin Brandstetter
Sep 2 '18 at 1:37
what does items cand mean
– CommonSenseCode
Aug 29 '18 at 17:20
what does items cand mean
– CommonSenseCode
Aug 29 '18 at 17:20
@commonSenseCode:
FROM items cand is short syntax for FROM items AS cand - cand is a table alias. The AS keyword is safe to omit for table aliases (as opposed to column aliases). See: dba.stackexchange.com/a/61455/3684 or stackoverflow.com/a/11543614/939860– Erwin Brandstetter
Sep 2 '18 at 1:37
@commonSenseCode:
FROM items cand is short syntax for FROM items AS cand - cand is a table alias. The AS keyword is safe to omit for table aliases (as opposed to column aliases). See: dba.stackexchange.com/a/61455/3684 or stackoverflow.com/a/11543614/939860– Erwin Brandstetter
Sep 2 '18 at 1:37
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f84472%2fhow-to-remove-object-from-json-array%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please be more specific: do you mean
'{"id":"note_1"}'or delete any element from the array that has'note_1'as key or value?– Erwin Brandstetter
Dec 6 '14 at 1:21
@ErwinBrandstetter, delete
{"id": "note_1", "full": "<url>", "thumb": "<url>"}where id = "note_1"– TSK
Dec 8 '14 at 17:26