Can't escape brackets concatenated with variables in Ansible

Question

I need to update records in a database table using Ansible, the value I need to put in the field is in the form : ["\/{{name}}"] where name is a variable.

So, if the value of name is Alex the updated value of the url column will be exactly ["\/Alex"].

I am doing this as below:

  - name: Update record
    postgresql_query:
      query: > 
        update table1 set url = %s
      positional_args:
      - '["\/{{name}}"]'

When I check the database after running the playbook, the value I found is: {'\\/Alex'}; the brackets are replaced by curly braces and the backslash is duplicated, and the double quotes are replaced by single ones.

I tried multiple work-arounds like escaping the brackets and the backslash: '\["\\/{{name}}"\]' and I also tried doing this: '{% raw %}["\/{% endraw %}{{name}}{% raw %}"]{% endraw %}' (took this from jinja2 docs), but none worked.

Answer 1

It seems you actually have to cast your name variable in a string, otherwise, Jinja is somehow interpreting your list item as a set — {'\/Alex'} is a set in Python.

So, your correct syntax would be:

- name: Update record
  postgresql_query:
    query: > 
      update table1 set url = %s
    positional_args:
      - '["\/{{ name | string }}"]'

---

Given the playbook:

- hosts: localhost
  gather_facts: no

  tasks:
    - postgresql_query:
        query: >-
          update table1 set url = %s
        positional_args:
          - '["\/{{ name | string }}"]'
      vars:
        name: Alex
      register: sql

    - debug: 
        var: sql.query

This yields, as expected:

TASK [postgresql_query] ******************************************************
changed: [localhost]

TASK [debug] *****************************************************************
ok: [localhost] => 
  sql.query: update table1 set url = '["\/Alex"]'