JSONDecode json format error from terraform

Question

Trying to add sql dbs created from azure portal to azure failover group.

Terraform block will call bash script :

data "external" "database_names" {
  program = ["sh", "${path.module}/scripts/fetch_db_id.sh"]
  query = {
    db_rg            = azurerm_resource_group.mssql.name
    server_name      = azurerm_mssql_server.mssqlserver.name
  }
}

fetch_db_id.sh bash script:

#!/usr/bin/env bash
# This script will get the database names at runtime.
eval "$(jq -r '@sh "export DB_RG=\(.db_rg) SERVER_NAME=\(.server_name)"')"

if [[ -z $DB_RG || -z $SERVER_NAME ]]; then
echo "Required variables DB_RG & SERVER_NAME not set" 1>&2
exit 1
fi

db_id=$(az sql db list --resource-group $DB_RG --server $SERVER_NAME --query [*].id | grep -v master 2>/dev/null)

jq -n --arg db_id "$db_id" '{"db_id":$db_id}'

unset DB_RG SERVER_NAME NODE_RG db_id

exit 0

Bash script output : Ran it locally on a linux VM without terraform :

"db_id": "[\n "/subscriptions/my_subscription_id/resourceGroups/sql_rg/providers/Microsoft.Sql/servers/sql_server_name/databases/databaseprd-db1",\n "/subscriptions/my_subscription_id/resourceGroups/sql_rg/providers/Microsoft.Sql/servers/sql_server_name/databases/databaseprd-db2",\n "/subscriptions/my_subscription_id/resourceGroups/sql_rg/providers/Microsoft.Sql/servers/sql_server_name/databases/databaseprd-db3"\n]" }

Terraform resource block to add database inside failover group:

resource "azurerm_sql_failover_group" "mssql_failover" {
  count                         = (var.enable_read_replica  && var.environment == "prd") ? 0 : 1
  name                          = var.mssql_failover_group
  resource_group_name           = azurerm_resource_group.mssql.name
  server_name                   = azurerm_mssql_server.mssqlserver.name
  databases                     = toset(jsondecode(data.external.database_names.result["db_id"]))
  partner_servers {
    id = azurerm_mssql_server.replica[0].id
  }

  read_write_endpoint_failover_policy {
    mode                        = "Automatic"
    grace_minutes               = 60
  }
  depends_on = [
    azurerm_mssql_server.replica
  ]
}

terraform error code: when executed via terraform jenkins pipeline

[1mdata.external.database_names.result["db_id"][0m is "[\n "/subscriptions/my_subscription_id/resourceGroups/sql_rg/providers/Microsoft.Sql/servers/sql_server_name/databases/databaseprd-db1",\n "/subscriptions/my_subscription_id/resourceGroups/sql_rg/providers/Microsoft.Sql/servers/sql_server_name/databases/databaseprd-db2",\n "/subscriptions/my_subscription_id/resourceGroups/sql_rg/providers/Microsoft.Sql/servers/sql_server_name/databases/databaseprd-db3",\n]" Call to function "jsondecode" failed: invalid character ']' looking for beginning of value

Note: it is introducing an extra "," when we run it with terraform jenkins pipeline, which could lead to json error.

You call out the extra comma, but you also have partial ANSI escape codes in there, the [1m and [0m. Are they really present or an artefact introduced by however you acquired the log? Similarly, is the italic and bold formatting on purpose or not? — Weeble
– Weeble, Commented Dec 24, 2021 at 15:47

Weeble · Accepted Answer · 2021-12-24 17:24:45Z

1

I don't think there's enough information here to be certain, but I can give a partial, speculative answer.

az sql db list --resource-group $DB_RG --server $SERVER_NAME --query [*].id | grep -v master 2>/dev/null

This looks suspicious: the az command outputs JSON, but you're filtering it with grep. What does the output from az look like here? Do you expect the result to be valid JSON?

You say that our output has a comma that you don't expect. This is what you'd expect to see if the az command spat out something like:

[
  "/blah/blah/databaseprd-db1",
  "/blah/blah/databaseprd-db2",
  "/blah/blah/databaseprd-db3",
  "/blah/blah/master"
]

The grep -v master would remove the line containing the term "master", leaving you with invalid JSON:

[
  "/blah/blah/databaseprd-db1",
  "/blah/blah/databaseprd-db2",
  "/blah/blah/databaseprd-db3",
]

If you want to use jq, you could replace the grep with something like

jq 'map(select(index("master")|not))'

edited Dec 24, 2021 at 17:24

answered Dec 24, 2021 at 15:44

Weeble

18.1k4 gold badges68 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mayank arora Over a year ago

issue was with grep -v leading to extra "," using

az sql db list --resource-group $DB_RG  --server $SERVER_NAME--qu ery [*].id | jq 'map(select(index("master")| not))'  2>/dev/null

resolved it.

Weeble Over a year ago

Oops, I will fix the answer there to include the not, I got mixed up. Glad to hear it got you on the right track.

Collectives™ on Stack Overflow

JSONDecode json format error from terraform

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related