# Insert a JSON containing double quoted Strings using SQL statment

**URL:** <https://forum.ansible.com/t/insert-a-json-containing-double-quoted-strings-using-sql-statment/10408>\
**Category:** Get Help\
**Tags:** json, mysql\
**Created:** [October 18, 2024, 10:03pm UTC](https://forum.ansible.com/t/insert-a-json-containing-double-quoted-strings-using-sql-statment/10408 "2024-10-18T22:03:01Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![botboy](https://avatars.discourse-cdn.com/v4/letter/b/ea5d25/32.png) [@botboy](https://forum.ansible.com/u/botboy)\
**Post date:** [October 18, 2024, 10:03pm UTC](https://forum.ansible.com/t/insert-a-json-containing-double-quoted-strings-using-sql-statment/10408/1 "2024-10-18T22:03:01Z")

</div>

I am trying to execute an Ansible play to execute a MySQL statement that inserts a JSON object where all the keys and values are Strings. However, with my current code, the resulting JSON does not have double quotes around the keys and values.

Current code:

```auto
- name: Execute SQL to update entries for client_id 'test')
    shell: >
      mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e 
      "UPDATE test_db.test SET app_home_url = '{{id_dns}}',
     
    app_callback_urls = '{"notification_callback_urls":{"app_novuid_registration":"{{id_dns}}/user_registration","forgot_password":"{{id_dns}}/password_reset"},"user_attribute_callback_url":"{{id_dns}}/user_updated","check_switch_callback_url":"{{id_dns}}/email_switched"}', 
    
    app_dns = '{{id_dns}}' 
    WHERE client_id = 'test';"

```

However, the result I am getting when I examine the results in MySQL does not contain double quotes around the Strings.

```auto
{ notification_callback_urls:  
     { app_novuid_registration : https://dns/user_registration, 
       forgot_password : https://dns/password_reset
     }, 
       user_attribute_callback_url : https://dns/user_updated, 
       check_switch_callback_url : https://dns/email_switched
}

```

Instead, I want this:

```auto
{"notification_callback_urls":  
     { "app_novuid_registration" : "https://dns/user_registration", 
       "forgot_password" : "https://dns/password_reset"
     }, 
       "user_attribute_callback_url" : "https://dns/user_updated", 
       "check_switch_callback_url" : "https://dns/email_switched"
}

```

I tried escaping the double quotes as so:

```auto
- name: Execute SQL to update entries for client_id 'test')
    shell: >
      mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e 
      "UPDATE test_db.test SET app_home_url = '{{id_dns}}',
     
    app_callback_urls = '{\"notification_callback_urls\":{\"app_novuid_registration\":\"{{id_dns}}/user_registration\",\"forgot_password\":\"{{id_dns}}/password_reset\"},\"user_attribute_callback_url\":\"{{id_dns}}/user_updated\",\check_switch_callback_url\":\"{{id_dns}}/email_switched\"}', 
    
    app_dns = '{{id_dns}}' 
    WHERE client_id = 'test';"

```

However, upon running I get an generic error, and it looks like trying to escape the double quotes with \" results in 3 slashes in the command that gets run.

```auto
TASK [Execute SQL to update entry] *********************************************
fatal: [localhost]: FAILED! => 
{
    "changed": true,
    "cmd": 
"mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e 
      "UPDATE test_db.test SET app_home_url = 'dns',
     
    app_callback_urls = '{\\\"notification_callback_urls\\\":{\\\"app_novuid_registration\\\":\\\"dns/user_registration\\\",\\\"forgot_password\\\":\\\"dns/password_reset\\\"},\\\"user_attribute_callback_url\\\":\\\"dns/user_updated\\\",\\\"check_switch_callback_url\\\":\\\"dns/email_switched\\\"}', 
    
    app_dns = 'dns' 
    WHERE client_id = 'test';"

```

Interestingly, just to try things out, I tried putting single quotes around the escaped `\"`, Example: `'\"'notification_callback_urls'\"' `

I get a JSON with values surrounded by single quotes, which still isn’t quite what I want, but it’s closer.

```auto
{'notification_callback_urls':        
     { 'app_novuid_registration' : 'https://dns/user_registration',         
      'forgot_password' : 'https://dns/password_reset'
      },
 'user_attribute_callback_url' : 'https://dns/user_updated',         
 'check_switch_callback_url' : 'https://dns/email_switched' 
}

```

I also tried setting `app_callback_urls` as a variable using set\_fact and then passing it into the string: `{{ app_callback_urls | to_json}}`. I get the same result as the original code (no quotes surrounding values in the JSON).

---

<div class="post-metadata">

**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\
**Post date:** [October 19, 2024, 1:11pm UTC](https://forum.ansible.com/t/insert-a-json-containing-double-quoted-strings-using-sql-statment/10408/2 "2024-10-19T13:11:04Z")

</div>

As you have suggested I’d use the `to_json` filter to manage the formatting and then I’d probably use the [community.mysql.mysql\_query module](https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_query_module.html), rather than `shell`, would something along these lines work?

```yaml
app_callback_urls:
  notification_callback_urls:
    app_novuid_registration: https://dns/user_registration
    forgot_password: https://dns/password_reset
  user_attribute_callback_url: https://dns/user_updated
  check_switch_callback_url: https://dns/email_switched

```

```yaml
- name: Execute SQL to update entries for client_id 'test'
  community.mysql.mysql_query: 
    query: "{{ mysql_query }}"
  vars:
    mysql_query: >-
      UPDATE test_db.test SET app_home_url =
      "{{ app_callback_urls | ansible.builtin.to_json }}"

```

---

<div class="post-metadata">

**Author:** ![utoddl](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/utoddl/32/249_2.png) [@utoddl](https://forum.ansible.com/u/utoddl)\
**Post date:** [October 20, 2024, 10:01pm UTC](https://forum.ansible.com/t/insert-a-json-containing-double-quoted-strings-using-sql-statment/10408/3 "2024-10-20T22:01:15Z")

</div>

Both of your examples are broken by indentation. “`app_callback_urls`” starts in the same column as “`shell:`”.

Fix the indentation, remove all the escapes, and you should be much better off.

What I don’t understand is why you got Ansible to run _at all_ with that indentation.

---

<div class="post-metadata">

**Author:** ![botboy](https://avatars.discourse-cdn.com/v4/letter/b/ea5d25/32.png) [@botboy](https://forum.ansible.com/u/botboy)\
**Post date:** [October 28, 2024, 11:19pm UTC](https://forum.ansible.com/t/insert-a-json-containing-double-quoted-strings-using-sql-statment/10408/4 "2024-10-28T23:19:55Z")

</div>

Updating with the solution. I had to surround the variable declarations with escaped backslashes with single quotes and then use to\_json function.

```auto

    - name: Set app_callback_urls 
      set_fact:
        app_callback_urls:
          '\"notification_callback_urls\"':
            '"\app_novuid_registration\"': '\"{{ dns }}/user_registration\"'
            '"\forgot_password\"': '\"{{ dns }}/password_reset\"'
          '"\user_attribute_callback_url\"': '\"{{ dns }}/user_updated\"'
          '"\check_switch_callback_url\"': '\"{{ dns }}/email_switched\"'

    - name: Execute SQL to update entries
      shell: >-
        mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e 
        "UPDATE veevaid.veevaid_app SET app_home_url = '{{ dns }}', app_callback_urls = '{{ app_callback_urls | to_json }}', app_dns = '{{ dns }}''"

```

---

<div class="post-metadata">

**Author:** ![utoddl](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/utoddl/32/249_2.png) [@utoddl](https://forum.ansible.com/u/utoddl)\
**Post date:** [October 29, 2024, 9:43pm UTC](https://forum.ansible.com/t/insert-a-json-containing-double-quoted-strings-using-sql-statment/10408/5 "2024-10-29T21:43:32Z")

</div>

I’ve been playing around with this off and on all day, trying to come up with a more readable and maintainable way to do this. (While I may claim that multiple instances of `'"\` instead of `'\"` in the prior post are evidence justifying the effort, mostly I enjoy quoting problems.)

One thing to note: If you don’t use your `app_callback_urls` fact anywhere else, you don’t need the `set_fact` task. Instead, you can create it as a task variable by adding a `vars:` section to your `[ansible.builtin.]shell:` task.

Also, as inadvertently demonstrated above, manually `\`-escaping a bunch of otherwise unnecessary double-quotes is error prone. This is much more easily and consistently accomplished with a `|regex_replace('"','\"')` filter after `to_json`.

For the example below, I don’t have a particular desire to execute the `mysql` command with these parameters, so I’ve inserted `printf "%q\n"` in front of it. The result is a shell script one-liner that prints what it sees as each parameter on separate lines. That in combination with `-vv` on `ansible-playbook`’s command line shows enough detail to feel pretty confident that things are being quoted as intended. (Whether it works with `mysql` is a separate question which, for me at least, is out of scope.)

One last thing to point out: Quoting is hard; nesting quotes multiplies that difficulty. In the example below, I broke out the “`escaped`” variable initialization with its `regex_replace` of double-quotes with escaped double-quotes to separate it from where the resulting escaped JSON gets used. There are two reasons I did this. The second reason is that it’s a whole lot easier to read and understand what it’s doing. But the primary reason is that, despite way more time spent on it than I care to admit, I could not incorporate that expression directly into the `shell` script body without `ansible-playbook` throwing errors about either unbalanced quotes or mismatched Jinja operators. Fortunately, I like it better this way.

```yaml
    - name: Execute SQL to update entries
      ansible.builtin.shell: >-
        printf "%q\n"
        mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e
        "UPDATE veevaid.veevaid_app SET app_home_url = '{{ dns }}',
        app_callback_urls = '{{ escaped }}',
        app_dns = '{{ dns }}'"
      vars:
        mysql_credentials_file_cmdline_parameter: mysql_credentials_file_cmdline_parameter
        dns: DnS
        app_callback_urls:
          notification_callback_urls:
            app_novuid_registration: '{{ dns }}/user_registration'
            forgot_password: '{{ dns }}/password_reset'
          user_attribute_callback_url: '{{ dns }}/user_updated'
          check_switch_callback_url: '{{ dns }}/email_switched'
        escaped: >-
          {{ app_callback_urls | to_json | regex_replace('"','\"') }}

```

And here’s the output from the job log:

```sh
TASK [Execute SQL to update entries] ***************************************************************************
task path: /home/utoddl/ansible/botboy_01.yml:7
changed: [localhost] => changed=true 
  cmd: 'printf "%q\n" mysql mysql_credentials_file_cmdline_parameter -s -N -e "UPDATE veevaid.veevaid_app SET app_home_url = ''DnS'', app_callback_urls = ''{\"notification_callback_urls\": {\"app_novuid_registration\": \"DnS/user_registration\", \"forgot_password\": \"DnS/password_reset\"}, \"user_attribute_callback_url\": \"DnS/user_updated\", \"check_switch_callback_url\": \"DnS/email_switched\"}'', app_dns = ''DnS''"'
  delta: '0:00:00.002708'
  end: '2024-10-29 17:40:42.634608'
  msg: ''
  rc: 0
  start: '2024-10-29 17:40:42.631900'
  stderr: ''
  stderr_lines: <omitted>
  stdout: |-
    mysql
    mysql_credentials_file_cmdline_parameter
    -s
    -N
    -e
    UPDATE\ veevaid.veevaid_app\ SET\ app_home_url\ =\ \'DnS\'\,\ app_callback_urls\ =\ \'\{\"notification_callback_urls\":\ \{\"app_novuid_registration\":\ \"DnS/user_registration\"\,\ \"forgot_password\":\ \"DnS/password_reset\"\}\,\ \"user_attribute_callback_url\":\ \"DnS/user_updated\"\,\ \"check_switch_callback_url\":\ \"DnS/email_switched\"\}\'\,\ app_dns\ =\ \'DnS\'
  stdout_lines: <omitted>

```