Updating TSQL Column From a JSON_VALUE in Another Table?
I have a database with two tables in it, one of which contains some information about a user who accessed a website, and the other containing a foreign key to the first table and a pile of JSON data about their request.
There's a problem with double-hops where it's recording the service account that made the request rather than the user name; since this UserName is recorded in the ModelData column of the other table, I'd like to correct these service accounts.
Here is what I've tried:
UPDATE Table_A
SET Table_A.CreatedBy = JSON_VALUE(Table_B.ModelData,'$.Action.UserName')
FROM Table_A
INNER JOIN Table_B ON (Table_B.Table_AID = Table_A.ID)
WHERE CreatedBy LIKE '%svc.%'
What I thought it would do:
- Where the
CreatedBycolumn is currently equal to a "svc" account, - Find the matching
ModelDatafrom the other table, - Pull the
UserNamefrom the JSON string, and - Assign it to
CreatedBy.
What it actually does is throw this error:
JSON text is not properly formatted. Unexpected character '.' is found at position 0
json
sql-server tsql edited Jan 1 at 9:34
marc_s
577k12911141259
asked Dec 31 '18 at 20:52
Erik LErik L
3115
|
show 1 more comment
I have a database with two tables in it, one of which contains some information about a user who accessed a website, and the other containing a foreign key to the first table and a pile of JSON data about their request.
There's a problem with double-hops where it's recording the service account that made the request rather than the user name; since this UserName is recorded in the ModelData column of the other table, I'd like to correct these service accounts.
Here is what I've tried:
UPDATE Table_A
SET Table_A.CreatedBy = JSON_VALUE(Table_B.ModelData,'$.Action.UserName')
FROM Table_A
INNER JOIN Table_B ON (Table_B.Table_AID = Table_A.ID)
WHERE CreatedBy LIKE '%svc.%'
What I thought it would do:
- Where the
CreatedBycolumn is currently equal to a "svc" account, - Find the matching
ModelDatafrom the other table, - Pull the
UserNamefrom the JSON string, and - Assign it to
CreatedBy.
What it actually does is throw this error:
JSON text is not properly formatted. Unexpected character '.' is found at position 0
json
sql-server tsql edited Jan 1 at 9:34
marc_s
577k12911141259
asked Dec 31 '18 at 20:52
Erik LErik L
3115
1
Doesselect count(*) from Table_B where ISJSON(ModelData) = 0return anything?
– LukStorms
Dec 31 '18 at 22:36
You have a problem with the data onTable_B.ModelDatain at least one record that fits the conditions of the query.
– Zohar Peled
Jan 1 at 4:55
@LukStorms Yes, it returned "8". I have been working on the assumption that if there was no '$.Action.UserName' or no JSON at all it would just skip the row, is that not the case?
– Erik L
Jan 2 at 15:27
1
@ErikL It means that there are 8 records that contain a string that's not a valid JSON. Hence that "not properly formatted" error. If it's valid, but there's no '$.Action.UserName' then I assume the CreatedBy will just be updated to NULL (haven't tested it). You could correct those invalid json's, Or add aAND ISJSON(Table_B.ModelData) = 1to your update query to avoid them.
– LukStorms
Jan 2 at 15:49
@LukStorms Thanks, you definitely got me on the right track with your two comments. I've done a SET ... = IIF(ISJSON(...)) with a IsNull(...) check inside of it, and now it's working perfectly. Thanks again!
– Erik L
Jan 2 at 16:35
|
show 1 more comment
I have a database with two tables in it, one of which contains some information about a user who accessed a website, and the other containing a foreign key to the first table and a pile of JSON data about their request.
There's a problem with double-hops where it's recording the service account that made the request rather than the user name; since this UserName is recorded in the ModelData column of the other table, I'd like to correct these service accounts.
Here is what I've tried:
UPDATE Table_A
SET Table_A.CreatedBy = JSON_VALUE(Table_B.ModelData,'$.Action.UserName')
FROM Table_A
INNER JOIN Table_B ON (Table_B.Table_AID = Table_A.ID)
WHERE CreatedBy LIKE '%svc.%'
What I thought it would do:
- Where the
CreatedBycolumn is currently equal to a "svc" account, - Find the matching
ModelDatafrom the other table, - Pull the
UserNamefrom the JSON string, and - Assign it to
CreatedBy.
What it actually does is throw this error:
JSON text is not properly formatted. Unexpected character '.' is found at position 0
json
sql-server tsql edited Jan 1 at 9:34
marc_s
577k12911141259
asked Dec 31 '18 at 20:52
Erik LErik L
3115
I have a database with two tables in it, one of which contains some information about a user who accessed a website, and the other containing a foreign key to the first table and a pile of JSON data about their request.
There's a problem with double-hops where it's recording the service account that made the request rather than the user name; since this UserName is recorded in the ModelData column of the other table, I'd like to correct these service accounts.
Here is what I've tried:
UPDATE Table_A
SET Table_A.CreatedBy = JSON_VALUE(Table_B.ModelData,'$.Action.UserName')
FROM Table_A
INNER JOIN Table_B ON (Table_B.Table_AID = Table_A.ID)
WHERE CreatedBy LIKE '%svc.%'
What I thought it would do:
- Where the
CreatedBycolumn is currently equal to a "svc" account, - Find the matching
ModelDatafrom the other table, - Pull the
UserNamefrom the JSON string, and - Assign it to
CreatedBy.
What it actually does is throw this error:
JSON text is not properly formatted. Unexpected character '.' is found at position 0
json
sql-server tsql json
sql-server tsql edited Jan 1 at 9:34
marc_s
577k12911141259
asked Dec 31 '18 at 20:52
Erik LErik L
3115
edited Jan 1 at 9:34
marc_s
577k12911141259
asked Dec 31 '18 at 20:52
Erik LErik L
3115
edited Jan 1 at 9:34
marc_s
577k12911141259
edited Jan 1 at 9:34
marc_s
577k12911141259
edited Jan 1 at 9:34
marc_s
577k12911141259
577k12911141259
asked Dec 31 '18 at 20:52
Erik LErik L
3115
asked Dec 31 '18 at 20:52
Erik LErik L
3115
asked Dec 31 '18 at 20:52
Erik LErik L
3115
3115
1
Doesselect count(*) from Table_B where ISJSON(ModelData) = 0return anything?
– LukStorms
Dec 31 '18 at 22:36
You have a problem with the data onTable_B.ModelDatain at least one record that fits the conditions of the query.
– Zohar Peled
Jan 1 at 4:55
@LukStorms Yes, it returned "8". I have been working on the assumption that if there was no '$.Action.UserName' or no JSON at all it would just skip the row, is that not the case?
– Erik L
Jan 2 at 15:27
1
@ErikL It means that there are 8 records that contain a string that's not a valid JSON. Hence that "not properly formatted" error. If it's valid, but there's no '$.Action.UserName' then I assume the CreatedBy will just be updated to NULL (haven't tested it). You could correct those invalid json's, Or add aAND ISJSON(Table_B.ModelData) = 1to your update query to avoid them.
– LukStorms
Jan 2 at 15:49
@LukStorms Thanks, you definitely got me on the right track with your two comments. I've done a SET ... = IIF(ISJSON(...)) with a IsNull(...) check inside of it, and now it's working perfectly. Thanks again!
– Erik L
Jan 2 at 16:35
|
show 1 more comment
1
Doesselect count(*) from Table_B where ISJSON(ModelData) = 0return anything?
– LukStorms
Dec 31 '18 at 22:36
You have a problem with the data onTable_B.ModelDatain at least one record that fits the conditions of the query.
– Zohar Peled
Jan 1 at 4:55
@LukStorms Yes, it returned "8". I have been working on the assumption that if there was no '$.Action.UserName' or no JSON at all it would just skip the row, is that not the case?
– Erik L
Jan 2 at 15:27
1
@ErikL It means that there are 8 records that contain a string that's not a valid JSON. Hence that "not properly formatted" error. If it's valid, but there's no '$.Action.UserName' then I assume the CreatedBy will just be updated to NULL (haven't tested it). You could correct those invalid json's, Or add aAND ISJSON(Table_B.ModelData) = 1to your update query to avoid them.
– LukStorms
Jan 2 at 15:49
@LukStorms Thanks, you definitely got me on the right track with your two comments. I've done a SET ... = IIF(ISJSON(...)) with a IsNull(...) check inside of it, and now it's working perfectly. Thanks again!
– Erik L
Jan 2 at 16:35
1
1
Does
select count(*) from Table_B where ISJSON(ModelData) = 0 return anything?– LukStorms
Dec 31 '18 at 22:36
Does
select count(*) from Table_B where ISJSON(ModelData) = 0 return anything?– LukStorms
Dec 31 '18 at 22:36
You have a problem with the data on
Table_B.ModelData in at least one record that fits the conditions of the query.– Zohar Peled
Jan 1 at 4:55
You have a problem with the data on
Table_B.ModelData in at least one record that fits the conditions of the query.– Zohar Peled
Jan 1 at 4:55
@LukStorms Yes, it returned "8". I have been working on the assumption that if there was no '$.Action.UserName' or no JSON at all it would just skip the row, is that not the case?
– Erik L
Jan 2 at 15:27
@LukStorms Yes, it returned "8". I have been working on the assumption that if there was no '$.Action.UserName' or no JSON at all it would just skip the row, is that not the case?
– Erik L
Jan 2 at 15:27
1
1
@ErikL It means that there are 8 records that contain a string that's not a valid JSON. Hence that "not properly formatted" error. If it's valid, but there's no '$.Action.UserName' then I assume the CreatedBy will just be updated to NULL (haven't tested it). You could correct those invalid json's, Or add a
AND ISJSON(Table_B.ModelData) = 1 to your update query to avoid them.– LukStorms
Jan 2 at 15:49
@ErikL It means that there are 8 records that contain a string that's not a valid JSON. Hence that "not properly formatted" error. If it's valid, but there's no '$.Action.UserName' then I assume the CreatedBy will just be updated to NULL (haven't tested it). You could correct those invalid json's, Or add a
AND ISJSON(Table_B.ModelData) = 1 to your update query to avoid them.– LukStorms
Jan 2 at 15:49
@LukStorms Thanks, you definitely got me on the right track with your two comments. I've done a SET ... = IIF(ISJSON(...)) with a IsNull(...) check inside of it, and now it's working perfectly. Thanks again!
– Erik L
Jan 2 at 16:35
@LukStorms Thanks, you definitely got me on the right track with your two comments. I've done a SET ... = IIF(ISJSON(...)) with a IsNull(...) check inside of it, and now it's working perfectly. Thanks again!
– Erik L
Jan 2 at 16:35
|
show 1 more comment
1 Answer
1
active
oldest
votes
LukStorms pointed out the problem in my assumptions; if the field was empty or there was no UserName to be found, the script would fail. Doing a couple of checks fixed this.
UPDATE Table_A
SET Table_A.CreatedBy = IIF(IsJson(Table_B.ModelData) > 0,
IsNull(JSON_VALUE(Table_B.ModelData,'$.Action.UserName'), Table_A.CreatedBy),
Table_A.CreatedBy)
FROM Table_A
INNER JOIN Table_B ON (Table_B.Table_AID = Table_A.ID)
WHERE CreatedBy LIKE '%svc.%'
First we need to make sure that the content of ModelData is actually a JSON string as opposed to normal text or blank space, than we also need to make sure the actual JSON value of $.Action.UserName isn't null. Once that's been done we can safely write the value.
answered Jan 2 at 16:41
Erik LErik L
3115
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%2f53991333%2fupdating-tsql-column-from-a-json-value-in-another-table%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
LukStorms pointed out the problem in my assumptions; if the field was empty or there was no UserName to be found, the script would fail. Doing a couple of checks fixed this.
UPDATE Table_A
SET Table_A.CreatedBy = IIF(IsJson(Table_B.ModelData) > 0,
IsNull(JSON_VALUE(Table_B.ModelData,'$.Action.UserName'), Table_A.CreatedBy),
Table_A.CreatedBy)
FROM Table_A
INNER JOIN Table_B ON (Table_B.Table_AID = Table_A.ID)
WHERE CreatedBy LIKE '%svc.%'
First we need to make sure that the content of ModelData is actually a JSON string as opposed to normal text or blank space, than we also need to make sure the actual JSON value of $.Action.UserName isn't null. Once that's been done we can safely write the value.
answered Jan 2 at 16:41
Erik LErik L
3115
add a comment |
LukStorms pointed out the problem in my assumptions; if the field was empty or there was no UserName to be found, the script would fail. Doing a couple of checks fixed this.
UPDATE Table_A
SET Table_A.CreatedBy = IIF(IsJson(Table_B.ModelData) > 0,
IsNull(JSON_VALUE(Table_B.ModelData,'$.Action.UserName'), Table_A.CreatedBy),
Table_A.CreatedBy)
FROM Table_A
INNER JOIN Table_B ON (Table_B.Table_AID = Table_A.ID)
WHERE CreatedBy LIKE '%svc.%'
First we need to make sure that the content of ModelData is actually a JSON string as opposed to normal text or blank space, than we also need to make sure the actual JSON value of $.Action.UserName isn't null. Once that's been done we can safely write the value.
answered Jan 2 at 16:41
Erik LErik L
3115
add a comment |
LukStorms pointed out the problem in my assumptions; if the field was empty or there was no UserName to be found, the script would fail. Doing a couple of checks fixed this.
UPDATE Table_A
SET Table_A.CreatedBy = IIF(IsJson(Table_B.ModelData) > 0,
IsNull(JSON_VALUE(Table_B.ModelData,'$.Action.UserName'), Table_A.CreatedBy),
Table_A.CreatedBy)
FROM Table_A
INNER JOIN Table_B ON (Table_B.Table_AID = Table_A.ID)
WHERE CreatedBy LIKE '%svc.%'
First we need to make sure that the content of ModelData is actually a JSON string as opposed to normal text or blank space, than we also need to make sure the actual JSON value of $.Action.UserName isn't null. Once that's been done we can safely write the value.
answered Jan 2 at 16:41
Erik LErik L
3115
LukStorms pointed out the problem in my assumptions; if the field was empty or there was no UserName to be found, the script would fail. Doing a couple of checks fixed this.
UPDATE Table_A
SET Table_A.CreatedBy = IIF(IsJson(Table_B.ModelData) > 0,
IsNull(JSON_VALUE(Table_B.ModelData,'$.Action.UserName'), Table_A.CreatedBy),
Table_A.CreatedBy)
FROM Table_A
INNER JOIN Table_B ON (Table_B.Table_AID = Table_A.ID)
WHERE CreatedBy LIKE '%svc.%'
First we need to make sure that the content of ModelData is actually a JSON string as opposed to normal text or blank space, than we also need to make sure the actual JSON value of $.Action.UserName isn't null. Once that's been done we can safely write the value.
answered Jan 2 at 16:41
Erik LErik L
3115
answered Jan 2 at 16:41
Erik LErik L
3115
answered Jan 2 at 16:41
Erik LErik L
3115
answered Jan 2 at 16:41
Erik LErik L
3115
3115
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%2f53991333%2fupdating-tsql-column-from-a-json-value-in-another-table%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
1
Does
select count(*) from Table_B where ISJSON(ModelData) = 0return anything?– LukStorms
Dec 31 '18 at 22:36
You have a problem with the data on
Table_B.ModelDatain at least one record that fits the conditions of the query.– Zohar Peled
Jan 1 at 4:55
@LukStorms Yes, it returned "8". I have been working on the assumption that if there was no '$.Action.UserName' or no JSON at all it would just skip the row, is that not the case?
– Erik L
Jan 2 at 15:27
1
@ErikL It means that there are 8 records that contain a string that's not a valid JSON. Hence that "not properly formatted" error. If it's valid, but there's no '$.Action.UserName' then I assume the CreatedBy will just be updated to NULL (haven't tested it). You could correct those invalid json's, Or add a
AND ISJSON(Table_B.ModelData) = 1to your update query to avoid them.– LukStorms
Jan 2 at 15:49
@LukStorms Thanks, you definitely got me on the right track with your two comments. I've done a SET ... = IIF(ISJSON(...)) with a IsNull(...) check inside of it, and now it's working perfectly. Thanks again!
– Erik L
Jan 2 at 16:35