Processing string representation of json within jq

Question

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?

You should fix the JSON at the source. This question might help with that. — 0stone0
– 0stone0, Commented Mar 24, 2022 at 15:56
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
– Weeble, Commented Mar 24, 2022 at 19:14

peak · Accepted Answer · 2022-03-25 01:56:50Z

1

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

< 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"
}

edited Mar 25, 2022 at 1:56

answered Mar 24, 2022 at 22:21

peak

119k21 gold badges185 silver badges218 bronze badges

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

1 Comment

pmf Over a year ago

String interpolation would be another way to wrap each line in quotes: jq -R '"\"\(.)\"" | fromjson | fromjson'

Collectives™ on Stack Overflow

Processing string representation of json within jq

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related