JQ: How To Change A Value in JSON with embedded keys

Question

I have the following JSON that is being returned from an AWS Secret:

{
    "ARN": "MyArn",
    "Name": "MySecret",
    "SecretString": "{\"ConnectionString\":\"MyConnectionString;\",\"SqlCommandTimeout\":\"60\",\",\"ServiceAccountPrincipal\":\"MyServicePrincipal\"}",
    "VersionStages": [
        "AWSCURRENT"
    ],
    "CreatedDate": "2022-01-13T09:08:55.442000-08:00"
}

I'd like to be able to modify the ConnectionString, the SqlCommandTimeout and the ServiceAccountPrincipal values. For brevity, I am just showing the change to the ConnectionString.

The "new" JSON would look like this:

{
    "ARN": "MyArn",
    "Name": "MySecret",
    "SecretString": "{\"ConnectionString\":\"MyNewConnectionString;\",\"SqlCommandTimeout\":\"60\",\",\"ServiceAccountPrincipal\":\"MyServicePrincipal\"}",
    "VersionStages": [
        "AWSCURRENT"
    ],
    "CreatedDate": "2022-01-13T09:08:55.442000-08:00"
}

However, I am not sure how to do it with JQ.

What I'd like to do is have JQ modify the values for each of these fields and output the JSON object with the updated values.

The problem I am having is modifying these values which are inside of embedded JSON object. I would expect something like this to work, but it doesn't:

cat json.txt | jq 'SecretString.ConnectionString = "MyNewConnectionString"'

I am getting this error:

jq: error (at <stdin>:8): Cannot index string with string "ConnectionString"
exit status 5

What I am doing wrong?

Thanks

Answer 1

SecretString is a field name in an object. Therefore you have to address it using a dot before its name: .SecretString

Also, the content of that field seems to be JSON but encoded as a string which you cannot address directly. You have to decode it first using fromjson and encode it again using tojson. However, the JSON string in your sample data has an error rendering it invalid JSON (after the value \"60\", there is an abundant quotation mark followed by a comma: \",). Assuming this is just a copy/paste error, let's continue:

To update a value using the value itself to begin with, use the update operator |=. To just assign a value use the simple assignment operator =.

Lastly, you can give jq the file to process as a parameter. You don't have to cat it first and then pipe it into jq.

Altogether, this is what you are looking for:

jq '.SecretString |= (fromjson | .ConnectionString = "MyNewConnectionString" | tojson)' json.txt

{
  "ARN": "MyArn",
  "Name": "MySecret",
  "SecretString": "{\"ConnectionString\":\"MyNewConnectionString\",\"SqlCommandTimeout\":\"60\",\"ServiceAccountPrincipal\":\"MyServicePrincipal\"}",
  "VersionStages": [
    "AWSCURRENT"
  ],
  "CreatedDate": "2022-01-13T09:08:55.442000-08:00"
}

Demo

Some additional notes:

If you want to keep the inner JSON as JSON (to access it more easily in th future), just drop the re-conversion with tojson)

To manipulate more than just one value, simply add them after the first: .ConnectionString = "…" | .SqlCommandTimeout = "…" | .ServiceAccountPrincipal = "…"

You can also provide the new value(s) as parameter(s) and access them as variable(s) instead, which makes your filter a little less cluttered, especially if you intend to change more than just that one field.

jq --arg v "MyNewConnectionString" '… .ConnectionString = $v …' json.txt

Answer 2

First off, note that your example SecretString is not valid JSON (there's a spurious ,"):

$ echo $ echo '{"ConnectionString":"MyConnectionString;","SqlCommandTimeout":"60",","ServiceAccountPrincipal":"MyServicePrincipal"}' | jq
parse error: Invalid numeric literal at line 1, column 94

If we fix that we can get to the meat of your question, which is dealing with nested JSON. jq provides a fromjson function for this purpose (along with tojson which we'll use in a moment), so we can get:

$ cat json.txt | jq '.SecretString | fromjson'
{
  "ConnectionString": "MyConnectionString;",
  "SqlCommandTimeout": "60",
  "ServiceAccountPrincipal": "MyServicePrincipal"
}

And we can set fields in the resulting JSON:

$ cat json.txt | jq '.SecretString | fromjson | .ConnectionString = "MyNewConnectionString"'
{
  "ConnectionString": "MyNewConnectionString",
  "SqlCommandTimeout": "60",
  "ServiceAccountPrincipal": "MyServicePrincipal"
}

I don't know if it's possible to apply this assignment to the original input inline (it might be!) but it's easy enough to just capture this output in a variable and then write it back to the original string:

# notice the tojson call at the end to escape the JSON as a string
$ new_secret=$(cat json.txt | jq '.SecretString | fromjson | .ConnectionString = "MyNewConnectionString" | tojson')

$ cat json.txt | jq ".SecretString = ${new_secret}"
{
  "ARN": "MyArn",
  "Name": "MySecret",
  "SecretString": "{\"ConnectionString\":\"MyNewConnectionString\",\"SqlCommandTimeout\":\"60\",\"ServiceAccountPrincipal\":\"MyServicePrincipal\"}",
  "VersionStages": [
    "AWSCURRENT"
  ],
  "CreatedDate": "2022-01-13T09:08:55.442000-08:00"
}

EDIT:

Ah neat, pmf's answer points out the |= operator which does exactly what you need :) I'll leave this answer up but pmf's approach is better.

Answer 3

If the problem is truly an upstream problem, then short of having it fixed, you could edit the malformed string in jq:

.SecretString
| sub( "MyConnectionString;\""; "MyConnectionString\"")
| sub( "60\",\""; "60\"")

using |=, fromjson, and tojson as appropriate, as described elsewhere on this page.

Answer 4

We have to use fromjson and tojson on the value of SecretString.

Filter

(.SecretString | fromjson | 
.ConnectionString |= "MyNewConnectionString" | tojson) as $secret 
| .SecretString |= $secret

Input

{
    "ARN": "MyArn",
    "Name": "MySecret",
    "SecretString": "{\"ConnectionString\":\"MyConnectionString\",\"SqlCommandTimeout\":\"60\",\"ServiceAccountPrincipal\":\"MyServicePrincipal\"}",
    "VersionStages": [
        "AWSCURRENT"
    ],
    "CreatedDate": "2022-01-13T09:08:55.442000-08:00"
}

Output

{
  "ARN": "MyArn",
  "Name": "MySecret",
  "SecretString": "{\"ConnectionString\":\"MyNewConnectionString\",\"SqlCommandTimeout\":\"60\",\"ServiceAccountPrincipal\":\"MyServicePrincipal\"}",
  "VersionStages": [
    "AWSCURRENT"
  ],
  "CreatedDate": "2022-01-13T09:08:55.442000-08:00"
}

You can see that ConnectionString is updated with the new value MyNewConnectionString as json string.

Demo

https://jqplay.org/s/HCsGdg1RLU