postgresql_query is not able use variable list as postgreSQL Json

[sivanagireddyb]

Summary

I am not able to use the variable list as postgreSQL Json argument in the psql query

PostgreSQL accept Json in format ["[email protected]","[email protected]"] On manipulating variable list ["[email protected]", "[email protected]"]

Error: Invalid input syntax for type json\n LINE 1:... Token \"'\" is invalid

Issue Type

Bug Report

Component Name

postgresql_query

Ansible Version

$ ansible --version

ansible 2.8.2

Configuration

# if using a version older than ansible-core 2.12 you should omit the '-t all'

OS / Environment

CENTOS

Steps to Reproduce

vars:
  contacts: ["[email protected]", "[email protected]" ]

tasks:
- name: Insert to db
  postgresql_query:
    db: xxx
    login_user: xxx
    login_password: xxx
    query: INSERT INTO test_table (id, contacts) VALUES (%s, %s)
    positional_args:
    - 1
    - '{{ contacts }}'

Expected Results

List variable should be inserted

Actual Results

Invalid input syntax for type json\n LINE 1:... Token \"'\" is invalid

[hunleyd]

thanks for the report @sivanagireddyb . I haven't looked at the code, but does using single-quotes instead of double-quotes fail w/ the same error?

[sivanagireddyb]

@hunleyd Yes, Double quotes are also giving same error. I had to use json filter and ascii format to make it work. '{{ contacts|to_json(ensure_ascii=False) }}' this worked for me.

[hunleyd]

ok thanks for the update @sivanagireddyb . we'll look into the issue and see if we can code up a fix.

[Andersson007]

@sivanagireddyb hello, thanks for reporting the issue. I'm not a Postgres JSON user. I found on https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/ that JSON should be inserted as a string of the format '{"key": "value"}' but I see a list in the description.

• Could you please elaborate a bit on why there's the list in the description?
• If you pass, say, the string {"email1": "[email protected]", "email2": "[email protected]"}, will it work?

thanks

[Andersson007]

@sivanagireddyb could you please take a look? ^ We're looking forward to your feedback

[sivanagireddyb]

Even though postgress type is Json it accepts list, Which worked for me when tested with hard-coded value instead of variable.

[Andersson007]

@sivanagireddyb if you pass the string {"email1": "[email protected]", "email2": "[email protected]"}, will it work?

[sivanagireddyb]

I haven't tested it but the the format I was expecting is in list []

[sivanagireddyb]

Using ascii filter along with json worked for me.

'{{ contacts|to_json(ensure_ascii=False) }}'