0

I have a function in bash psql_json() that hits a postgres database and returns a json. I can not edit this bash function but it injects a postgres statement into this this query which is then sent to the db:

"select row_to_json(t)::json from ($arg) t;"

Where arg is some statement ie:

select * from table

However, this function returns a weirdly formatted json string as seen below:

{\"id\":1,\"firstName\":\"firstName1\",\"lastName\":\"lastName1\"}\n
{\"id\":2,\"firstName\":\"firstName2\",\"lastName\":\"lastName2\"}

The above output is what happens after running these statements:

local_fn="$(mktemp)"
psql_json 'select * from table' > "$local_fn"
cat "$local_fn"

Now when I try to put this json as is into jq, I get the following error:

cat json1.json | jq '.'

jq: error: syntax error, unexpected $end, expecting ';' or ')' (Unix shell quoting issues?)

I found this thread which seems to indicate the issue is that I am passing a string into jq which it doesnt like and is unable to parse, so I tried both:

cat json1.json | jq 'fromjson]'
cat json1.json | jq '[.[]|fromjson]'

and they both return

parse error: Invalid numeric literal at line 1, column 3

Is there any way I can get string representation of the json above into jq in a clean way to process it or would I need to clean/edit the string in bash?

Jengels
  • 440
  • 6
  • 15
  • 1
    You should fix the JSON at the source. [This question](https://stackoverflow.com/questions/26998058/postgresql-to-json-function-escapes-all-doublequote-characters) might help with that. – 0stone0 Mar 24 '22 at 15:56
  • 1
    Could you clarify, does the bash function literally emit the characters you posted there, including a backslash, a letter `n`, and a real newline? Can you share what's inside the bash function? As @0stone0 says, it really would be best to fix the thing that's producing this, because it's not terribly clear what format it is or how it would encode other characters. If it's impossible to fix, more information about its source would help avoid a fragile solution that breaks in the future. – Weeble Mar 24 '22 at 19:14
  • Added some more context from the bash used – Jengels Mar 24 '22 at 19:50

1 Answers1

1

You could fix the input using jq or a text-processing tool such as :

< weird.json jq -R 'sub("^";"\"") | sub("$";"\"") | fromjson | fromjson'

or

< weird.json sed -e 's/^/"/' -e 's/$/"/' | jq -R 'fromjson|fromjson'

With your input, the result in both cases is:

{
  "id": 1,
  "firstName": "firstName1",
  "lastName": "lastName1"
}
{
  "id": 2,
  "firstName": "firstName2",
  "lastName": "lastName2"
}
peak
  • 105,803
  • 17
  • 152
  • 177
  • 2
    String interpolation would be another way to wrap each line in quotes: `jq -R '"\"\(.)\"" | fromjson | fromjson'` – pmf Mar 25 '22 at 04:02