How to update multiple json fields at root level with Postgres?
I am trying to update the fields age and city of one json feed using:
select jsonb_set(d,'{0,age,city}',d || '{"age":30,"city":"los angeles"}')
from (
values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);
but what I get back is:
{"age": 26, "city": "new york city", "name": "john"}
instead of the expected:
{"age": 30, "city": "los angeles", "name": "john"}
that means none of the wanted fields have been updated.
I have already looked at:
postgres jsonb_set multiple keys update
and went through the relative documentation but I cannot get it right. Any help?
json postgresql jsonb
edited Jan 23 at 19:59
klin
59.2k65283
asked Nov 23 '18 at 18:32
RandomizeRandomize
3,322164191
add a comment |
I am trying to update the fields age and city of one json feed using:
select jsonb_set(d,'{0,age,city}',d || '{"age":30,"city":"los angeles"}')
from (
values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);
but what I get back is:
{"age": 26, "city": "new york city", "name": "john"}
instead of the expected:
{"age": 30, "city": "los angeles", "name": "john"}
that means none of the wanted fields have been updated.
I have already looked at:
postgres jsonb_set multiple keys update
and went through the relative documentation but I cannot get it right. Any help?
json postgresql jsonb
edited Jan 23 at 19:59
klin
59.2k65283
asked Nov 23 '18 at 18:32
RandomizeRandomize
3,322164191
add a comment |
I am trying to update the fields age and city of one json feed using:
select jsonb_set(d,'{0,age,city}',d || '{"age":30,"city":"los angeles"}')
from (
values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);
but what I get back is:
{"age": 26, "city": "new york city", "name": "john"}
instead of the expected:
{"age": 30, "city": "los angeles", "name": "john"}
that means none of the wanted fields have been updated.
I have already looked at:
postgres jsonb_set multiple keys update
and went through the relative documentation but I cannot get it right. Any help?
json postgresql jsonb
edited Jan 23 at 19:59
klin
59.2k65283
asked Nov 23 '18 at 18:32
RandomizeRandomize
3,322164191
I am trying to update the fields age and city of one json feed using:
select jsonb_set(d,'{0,age,city}',d || '{"age":30,"city":"los angeles"}')
from (
values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);
but what I get back is:
{"age": 26, "city": "new york city", "name": "john"}
instead of the expected:
{"age": 30, "city": "los angeles", "name": "john"}
that means none of the wanted fields have been updated.
I have already looked at:
postgres jsonb_set multiple keys update
and went through the relative documentation but I cannot get it right. Any help?
json postgresql jsonb
json postgresql jsonb
edited Jan 23 at 19:59
klin
59.2k65283
asked Nov 23 '18 at 18:32
RandomizeRandomize
3,322164191
edited Jan 23 at 19:59
klin
59.2k65283
asked Nov 23 '18 at 18:32
RandomizeRandomize
3,322164191
edited Jan 23 at 19:59
klin
59.2k65283
edited Jan 23 at 19:59
klin
59.2k65283
edited Jan 23 at 19:59
klin
59.2k65283
59.2k65283
asked Nov 23 '18 at 18:32
RandomizeRandomize
3,322164191
asked Nov 23 '18 at 18:32
RandomizeRandomize
3,322164191
asked Nov 23 '18 at 18:32
RandomizeRandomize
3,322164191
3,322164191
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
From the documentation:
All the items of the
pathparameter ofjsonb_setas well asjsonb_insertexcept the last item must be present in the target.
The path given in the query does not meet the above condition. Actually, jsonb_set() does not work for objects at the root level, and the only way is to use the || operator:
select d || '{"age":30,"city":"los angeles"}'
from (
values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);
?column?
----------------------------------------------------
{"age": 30, "city": "los angeles", "name": "john"}
(1 row)
Maybe it would be logical that you could use an empty path
select jsonb_set(d, '{}', d || '{"age":30,"city":"los angeles"}')
Unfortunately, jsonb developers did not provide such a possibility.
answered Nov 23 '18 at 19:50
klinklin
59.2k65283
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53451511%2fhow-to-update-multiple-json-fields-at-root-level-with-postgres%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
From the documentation:
All the items of the
pathparameter ofjsonb_setas well asjsonb_insertexcept the last item must be present in the target.
The path given in the query does not meet the above condition. Actually, jsonb_set() does not work for objects at the root level, and the only way is to use the || operator:
select d || '{"age":30,"city":"los angeles"}'
from (
values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);
?column?
----------------------------------------------------
{"age": 30, "city": "los angeles", "name": "john"}
(1 row)
Maybe it would be logical that you could use an empty path
select jsonb_set(d, '{}', d || '{"age":30,"city":"los angeles"}')
Unfortunately, jsonb developers did not provide such a possibility.
answered Nov 23 '18 at 19:50
klinklin
59.2k65283
add a comment |
From the documentation:
All the items of the
pathparameter ofjsonb_setas well asjsonb_insertexcept the last item must be present in the target.
The path given in the query does not meet the above condition. Actually, jsonb_set() does not work for objects at the root level, and the only way is to use the || operator:
select d || '{"age":30,"city":"los angeles"}'
from (
values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);
?column?
----------------------------------------------------
{"age": 30, "city": "los angeles", "name": "john"}
(1 row)
Maybe it would be logical that you could use an empty path
select jsonb_set(d, '{}', d || '{"age":30,"city":"los angeles"}')
Unfortunately, jsonb developers did not provide such a possibility.
answered Nov 23 '18 at 19:50
klinklin
59.2k65283
add a comment |
From the documentation:
All the items of the
pathparameter ofjsonb_setas well asjsonb_insertexcept the last item must be present in the target.
The path given in the query does not meet the above condition. Actually, jsonb_set() does not work for objects at the root level, and the only way is to use the || operator:
select d || '{"age":30,"city":"los angeles"}'
from (
values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);
?column?
----------------------------------------------------
{"age": 30, "city": "los angeles", "name": "john"}
(1 row)
Maybe it would be logical that you could use an empty path
select jsonb_set(d, '{}', d || '{"age":30,"city":"los angeles"}')
Unfortunately, jsonb developers did not provide such a possibility.
answered Nov 23 '18 at 19:50
klinklin
59.2k65283
From the documentation:
All the items of the
pathparameter ofjsonb_setas well asjsonb_insertexcept the last item must be present in the target.
The path given in the query does not meet the above condition. Actually, jsonb_set() does not work for objects at the root level, and the only way is to use the || operator:
select d || '{"age":30,"city":"los angeles"}'
from (
values ('{"name":"john", "age":26,"city":"new york city"}'::jsonb)
) t(d);
?column?
----------------------------------------------------
{"age": 30, "city": "los angeles", "name": "john"}
(1 row)
Maybe it would be logical that you could use an empty path
select jsonb_set(d, '{}', d || '{"age":30,"city":"los angeles"}')
Unfortunately, jsonb developers did not provide such a possibility.
answered Nov 23 '18 at 19:50
klinklin
59.2k65283
answered Nov 23 '18 at 19:50
klinklin
59.2k65283
answered Nov 23 '18 at 19:50
klinklin
59.2k65283
answered Nov 23 '18 at 19:50
klinklin
59.2k65283
59.2k65283
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
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%2fstackoverflow.com%2fquestions%2f53451511%2fhow-to-update-multiple-json-fields-at-root-level-with-postgres%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
