PostgreSQL jsonb update multiple nested fields
I table that has an id field and a jsonb field in a postgresql db. The jsonb has a structure that looks something like this:
{
"id": "some-id",
"lastUpdated": "2018-10-24T10:36:29.174Z",
"counters": {
"counter1": 100,
"counter2": 200
}
}
What I need to do is update the lastModified and one of the counters:
def update(id: String, counter: Option[String])
So for example if I do update("some-id", Some("counter2")) I need the lastUpdated to be the current date time and counter2 to be incremented to 201.
I'm using ScalikeJDBC and this is where I got so far:
def update(id: String, counter: Option[String]): Option[ApiKey] = DB localTx { implicit session =>
val update =
if(counter.isDefined)
sqls"""'{"lastUpdated": ${DateTime.now()}, "counters": {'${counter.get}: COALESCE('counters'->>${counter.get},'0')::int'}'"""
else
sqls"""'{"lastUpdated": ${DateTime.now()}}'"""
sql"UPDATE apiKey SET content = content || $update WHERE id = $key".update().apply()
}
But I get the following error:
org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3
I've tried other approaches, but I wasn't able to make it work. Is it possible to write this as a single query?
Here's a broken fiddle to help with testing https://www.db-fiddle.com/f/bsteTUMXDGDSHp32fw2Zop/1
sql postgresql scala scalikejdbc
asked Nov 22 '18 at 9:17
Maria LiviaMaria Livia
3317
add a comment |
I table that has an id field and a jsonb field in a postgresql db. The jsonb has a structure that looks something like this:
{
"id": "some-id",
"lastUpdated": "2018-10-24T10:36:29.174Z",
"counters": {
"counter1": 100,
"counter2": 200
}
}
What I need to do is update the lastModified and one of the counters:
def update(id: String, counter: Option[String])
So for example if I do update("some-id", Some("counter2")) I need the lastUpdated to be the current date time and counter2 to be incremented to 201.
I'm using ScalikeJDBC and this is where I got so far:
def update(id: String, counter: Option[String]): Option[ApiKey] = DB localTx { implicit session =>
val update =
if(counter.isDefined)
sqls"""'{"lastUpdated": ${DateTime.now()}, "counters": {'${counter.get}: COALESCE('counters'->>${counter.get},'0')::int'}'"""
else
sqls"""'{"lastUpdated": ${DateTime.now()}}'"""
sql"UPDATE apiKey SET content = content || $update WHERE id = $key".update().apply()
}
But I get the following error:
org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3
I've tried other approaches, but I wasn't able to make it work. Is it possible to write this as a single query?
Here's a broken fiddle to help with testing https://www.db-fiddle.com/f/bsteTUMXDGDSHp32fw2Zop/1
sql postgresql scala scalikejdbc
asked Nov 22 '18 at 9:17
Maria LiviaMaria Livia
3317
I don't have experience with ScalikeJDBC, but the Postgres query can look like this: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/1
– Ionuț G. Stan
Nov 22 '18 at 10:41
Or maybe this, to get rid of the doublejsonb_setcall: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/2 The problem with||is that it's not doing a recursive merge.
– Ionuț G. Stan
Nov 22 '18 at 10:44
That makes sense, I'll give it a go, thanks!
– Maria Livia
Nov 22 '18 at 10:56
And a version that updates the nested counter only if an update exists: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/3 I realize I can't really answer your question because I don't know how ScalikeJDBC works, but I hope it helps.
– Ionuț G. Stan
Nov 22 '18 at 10:56
Actually this was very helpful as I finally managed to write a query that does what I want: db-fiddle.com/f/UiGJyQo1Zp45N6nYeVZdK/0 Unfortunately I still can't make it work in Scalike, but it's a start.
– Maria Livia
Nov 22 '18 at 14:32
add a comment |
I table that has an id field and a jsonb field in a postgresql db. The jsonb has a structure that looks something like this:
{
"id": "some-id",
"lastUpdated": "2018-10-24T10:36:29.174Z",
"counters": {
"counter1": 100,
"counter2": 200
}
}
What I need to do is update the lastModified and one of the counters:
def update(id: String, counter: Option[String])
So for example if I do update("some-id", Some("counter2")) I need the lastUpdated to be the current date time and counter2 to be incremented to 201.
I'm using ScalikeJDBC and this is where I got so far:
def update(id: String, counter: Option[String]): Option[ApiKey] = DB localTx { implicit session =>
val update =
if(counter.isDefined)
sqls"""'{"lastUpdated": ${DateTime.now()}, "counters": {'${counter.get}: COALESCE('counters'->>${counter.get},'0')::int'}'"""
else
sqls"""'{"lastUpdated": ${DateTime.now()}}'"""
sql"UPDATE apiKey SET content = content || $update WHERE id = $key".update().apply()
}
But I get the following error:
org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3
I've tried other approaches, but I wasn't able to make it work. Is it possible to write this as a single query?
Here's a broken fiddle to help with testing https://www.db-fiddle.com/f/bsteTUMXDGDSHp32fw2Zop/1
sql postgresql scala scalikejdbc
asked Nov 22 '18 at 9:17
Maria LiviaMaria Livia
3317
I table that has an id field and a jsonb field in a postgresql db. The jsonb has a structure that looks something like this:
{
"id": "some-id",
"lastUpdated": "2018-10-24T10:36:29.174Z",
"counters": {
"counter1": 100,
"counter2": 200
}
}
What I need to do is update the lastModified and one of the counters:
def update(id: String, counter: Option[String])
So for example if I do update("some-id", Some("counter2")) I need the lastUpdated to be the current date time and counter2 to be incremented to 201.
I'm using ScalikeJDBC and this is where I got so far:
def update(id: String, counter: Option[String]): Option[ApiKey] = DB localTx { implicit session =>
val update =
if(counter.isDefined)
sqls"""'{"lastUpdated": ${DateTime.now()}, "counters": {'${counter.get}: COALESCE('counters'->>${counter.get},'0')::int'}'"""
else
sqls"""'{"lastUpdated": ${DateTime.now()}}'"""
sql"UPDATE apiKey SET content = content || $update WHERE id = $key".update().apply()
}
But I get the following error:
org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3
I've tried other approaches, but I wasn't able to make it work. Is it possible to write this as a single query?
Here's a broken fiddle to help with testing https://www.db-fiddle.com/f/bsteTUMXDGDSHp32fw2Zop/1
sql postgresql scala scalikejdbc
sql postgresql scala scalikejdbc
asked Nov 22 '18 at 9:17
Maria LiviaMaria Livia
3317
asked Nov 22 '18 at 9:17
Maria LiviaMaria Livia
3317
edited Nov 22 '18 at 10:12
Maria Livia
asked Nov 22 '18 at 9:17
Maria LiviaMaria Livia
3317
asked Nov 22 '18 at 9:17
Maria LiviaMaria Livia
3317
asked Nov 22 '18 at 9:17
Maria LiviaMaria Livia
3317
3317
I don't have experience with ScalikeJDBC, but the Postgres query can look like this: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/1
– Ionuț G. Stan
Nov 22 '18 at 10:41
Or maybe this, to get rid of the doublejsonb_setcall: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/2 The problem with||is that it's not doing a recursive merge.
– Ionuț G. Stan
Nov 22 '18 at 10:44
That makes sense, I'll give it a go, thanks!
– Maria Livia
Nov 22 '18 at 10:56
And a version that updates the nested counter only if an update exists: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/3 I realize I can't really answer your question because I don't know how ScalikeJDBC works, but I hope it helps.
– Ionuț G. Stan
Nov 22 '18 at 10:56
Actually this was very helpful as I finally managed to write a query that does what I want: db-fiddle.com/f/UiGJyQo1Zp45N6nYeVZdK/0 Unfortunately I still can't make it work in Scalike, but it's a start.
– Maria Livia
Nov 22 '18 at 14:32
add a comment |
I don't have experience with ScalikeJDBC, but the Postgres query can look like this: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/1
– Ionuț G. Stan
Nov 22 '18 at 10:41
Or maybe this, to get rid of the doublejsonb_setcall: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/2 The problem with||is that it's not doing a recursive merge.
– Ionuț G. Stan
Nov 22 '18 at 10:44
That makes sense, I'll give it a go, thanks!
– Maria Livia
Nov 22 '18 at 10:56
And a version that updates the nested counter only if an update exists: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/3 I realize I can't really answer your question because I don't know how ScalikeJDBC works, but I hope it helps.
– Ionuț G. Stan
Nov 22 '18 at 10:56
Actually this was very helpful as I finally managed to write a query that does what I want: db-fiddle.com/f/UiGJyQo1Zp45N6nYeVZdK/0 Unfortunately I still can't make it work in Scalike, but it's a start.
– Maria Livia
Nov 22 '18 at 14:32
I don't have experience with ScalikeJDBC, but the Postgres query can look like this: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/1
– Ionuț G. Stan
Nov 22 '18 at 10:41
I don't have experience with ScalikeJDBC, but the Postgres query can look like this: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/1
– Ionuț G. Stan
Nov 22 '18 at 10:41
Or maybe this, to get rid of the double
jsonb_set call: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/2 The problem with || is that it's not doing a recursive merge.– Ionuț G. Stan
Nov 22 '18 at 10:44
Or maybe this, to get rid of the double
jsonb_set call: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/2 The problem with || is that it's not doing a recursive merge.– Ionuț G. Stan
Nov 22 '18 at 10:44
That makes sense, I'll give it a go, thanks!
– Maria Livia
Nov 22 '18 at 10:56
That makes sense, I'll give it a go, thanks!
– Maria Livia
Nov 22 '18 at 10:56
And a version that updates the nested counter only if an update exists: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/3 I realize I can't really answer your question because I don't know how ScalikeJDBC works, but I hope it helps.
– Ionuț G. Stan
Nov 22 '18 at 10:56
And a version that updates the nested counter only if an update exists: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/3 I realize I can't really answer your question because I don't know how ScalikeJDBC works, but I hope it helps.
– Ionuț G. Stan
Nov 22 '18 at 10:56
Actually this was very helpful as I finally managed to write a query that does what I want: db-fiddle.com/f/UiGJyQo1Zp45N6nYeVZdK/0 Unfortunately I still can't make it work in Scalike, but it's a start.
– Maria Livia
Nov 22 '18 at 14:32
Actually this was very helpful as I finally managed to write a query that does what I want: db-fiddle.com/f/UiGJyQo1Zp45N6nYeVZdK/0 Unfortunately I still can't make it work in Scalike, but it's a start.
– Maria Livia
Nov 22 '18 at 14:32
add a comment |
1 Answer
1
active
oldest
votes
I don't know a lot about PostgreSQL's jsonb type, but it seems impossible to pass everything as bind parameters in a JDBC PreparedStatement. I have to say that you may have to use SQLSyntax.createUnsafely to bypass PreparedStatement as below:
def update(id: String, counter: Option[String]): Unit = DB localTx { implicit session =>
val now = java.time.ZonedDateTime.now.toOffsetDateTime.toString
val q: SQLSyntax = counter match {
case Some(c) =>
val content: String =
s"""
jsonb_set(
content || '{"lastUsed": "${now}"}',
'{counters, $c}',
(COALESCE(content->'counters'->>'$c','0')::int + 1)::text::jsonb
)
"""
SQLSyntax.createUnsafely(s"""
UPDATE
example
SET
content = ${content}
WHERE
id = '$id';
""")
case _ =>
throw new RuntimeException
}
sql"$q".update.apply()
}
update("73c1fa11-bf2f-42c9-80fd-c70ac123fca9", Some("counter2"))
answered Nov 22 '18 at 15:21
Kazuhiro SeraKazuhiro Sera
1,559914
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%2f53427464%2fpostgresql-jsonb-update-multiple-nested-fields%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
I don't know a lot about PostgreSQL's jsonb type, but it seems impossible to pass everything as bind parameters in a JDBC PreparedStatement. I have to say that you may have to use SQLSyntax.createUnsafely to bypass PreparedStatement as below:
def update(id: String, counter: Option[String]): Unit = DB localTx { implicit session =>
val now = java.time.ZonedDateTime.now.toOffsetDateTime.toString
val q: SQLSyntax = counter match {
case Some(c) =>
val content: String =
s"""
jsonb_set(
content || '{"lastUsed": "${now}"}',
'{counters, $c}',
(COALESCE(content->'counters'->>'$c','0')::int + 1)::text::jsonb
)
"""
SQLSyntax.createUnsafely(s"""
UPDATE
example
SET
content = ${content}
WHERE
id = '$id';
""")
case _ =>
throw new RuntimeException
}
sql"$q".update.apply()
}
update("73c1fa11-bf2f-42c9-80fd-c70ac123fca9", Some("counter2"))
answered Nov 22 '18 at 15:21
Kazuhiro SeraKazuhiro Sera
1,559914
add a comment |
I don't know a lot about PostgreSQL's jsonb type, but it seems impossible to pass everything as bind parameters in a JDBC PreparedStatement. I have to say that you may have to use SQLSyntax.createUnsafely to bypass PreparedStatement as below:
def update(id: String, counter: Option[String]): Unit = DB localTx { implicit session =>
val now = java.time.ZonedDateTime.now.toOffsetDateTime.toString
val q: SQLSyntax = counter match {
case Some(c) =>
val content: String =
s"""
jsonb_set(
content || '{"lastUsed": "${now}"}',
'{counters, $c}',
(COALESCE(content->'counters'->>'$c','0')::int + 1)::text::jsonb
)
"""
SQLSyntax.createUnsafely(s"""
UPDATE
example
SET
content = ${content}
WHERE
id = '$id';
""")
case _ =>
throw new RuntimeException
}
sql"$q".update.apply()
}
update("73c1fa11-bf2f-42c9-80fd-c70ac123fca9", Some("counter2"))
answered Nov 22 '18 at 15:21
Kazuhiro SeraKazuhiro Sera
1,559914
add a comment |
I don't know a lot about PostgreSQL's jsonb type, but it seems impossible to pass everything as bind parameters in a JDBC PreparedStatement. I have to say that you may have to use SQLSyntax.createUnsafely to bypass PreparedStatement as below:
def update(id: String, counter: Option[String]): Unit = DB localTx { implicit session =>
val now = java.time.ZonedDateTime.now.toOffsetDateTime.toString
val q: SQLSyntax = counter match {
case Some(c) =>
val content: String =
s"""
jsonb_set(
content || '{"lastUsed": "${now}"}',
'{counters, $c}',
(COALESCE(content->'counters'->>'$c','0')::int + 1)::text::jsonb
)
"""
SQLSyntax.createUnsafely(s"""
UPDATE
example
SET
content = ${content}
WHERE
id = '$id';
""")
case _ =>
throw new RuntimeException
}
sql"$q".update.apply()
}
update("73c1fa11-bf2f-42c9-80fd-c70ac123fca9", Some("counter2"))
answered Nov 22 '18 at 15:21
Kazuhiro SeraKazuhiro Sera
1,559914
I don't know a lot about PostgreSQL's jsonb type, but it seems impossible to pass everything as bind parameters in a JDBC PreparedStatement. I have to say that you may have to use SQLSyntax.createUnsafely to bypass PreparedStatement as below:
def update(id: String, counter: Option[String]): Unit = DB localTx { implicit session =>
val now = java.time.ZonedDateTime.now.toOffsetDateTime.toString
val q: SQLSyntax = counter match {
case Some(c) =>
val content: String =
s"""
jsonb_set(
content || '{"lastUsed": "${now}"}',
'{counters, $c}',
(COALESCE(content->'counters'->>'$c','0')::int + 1)::text::jsonb
)
"""
SQLSyntax.createUnsafely(s"""
UPDATE
example
SET
content = ${content}
WHERE
id = '$id';
""")
case _ =>
throw new RuntimeException
}
sql"$q".update.apply()
}
update("73c1fa11-bf2f-42c9-80fd-c70ac123fca9", Some("counter2"))
answered Nov 22 '18 at 15:21
Kazuhiro SeraKazuhiro Sera
1,559914
edited Dec 8 '18 at 5:21
answered Nov 22 '18 at 15:21
Kazuhiro SeraKazuhiro Sera
1,559914
answered Nov 22 '18 at 15:21
Kazuhiro SeraKazuhiro Sera
1,559914
answered Nov 22 '18 at 15:21
Kazuhiro SeraKazuhiro Sera
1,559914
1,559914
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%2f53427464%2fpostgresql-jsonb-update-multiple-nested-fields%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
I don't have experience with ScalikeJDBC, but the Postgres query can look like this: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/1
– Ionuț G. Stan
Nov 22 '18 at 10:41
Or maybe this, to get rid of the double
jsonb_setcall: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/2 The problem with||is that it's not doing a recursive merge.– Ionuț G. Stan
Nov 22 '18 at 10:44
That makes sense, I'll give it a go, thanks!
– Maria Livia
Nov 22 '18 at 10:56
And a version that updates the nested counter only if an update exists: db-fiddle.com/f/mSugD6cNPc5EiujCtRjZjd/3 I realize I can't really answer your question because I don't know how ScalikeJDBC works, but I hope it helps.
– Ionuț G. Stan
Nov 22 '18 at 10:56
Actually this was very helpful as I finally managed to write a query that does what I want: db-fiddle.com/f/UiGJyQo1Zp45N6nYeVZdK/0 Unfortunately I still can't make it work in Scalike, but it's a start.
– Maria Livia
Nov 22 '18 at 14:32