Insert a JSON containing double quoted Strings using SQL statment

Get Help
,
1

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:

- 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.

{ 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:

{"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:

- 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.

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.

{'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).

1 Like
2

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, rather than shell, would something along these lines work?

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

- 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 }}"
1 Like
3

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.

2 Likes