Pg-promise: Regexp Replace issue

[UJPrasad]

Expected behavior

I've used a query (below) with oneOrNone which has regex_replace

with cte as (select json_build_object(
  upper(
    regexp_replace(
      replace(table.column, ' ', '_'), 
   '[^\w]+','','g')
  ), table.*
) as c from table)
select jsonb_object_agg(t.k, t.v) as exp
from cte, json_each(cte.c) as t(k,v)
````

on pgAdmin and CLI, I got results as expected

Example: 

{
"exp": {
"COLUM_NAME": {....}
"COLUM_NAME_2": {....}
}
}

### Actual behavior
But when I used it in `oneOrNone` , I'm getting results as

{
"exp": {
"": {....}
}
}
```

Only last row details are getting in an empty key

Steps to reproduce

Create a table with n fields, and choose one with text data type which has some special chars as json_object_build key

Environment

• Version of pg-promise: 10.3.5
• OS type (Linux/Windows/Mac): Linux
• Version of Node.js: 12.13.1

[vitaly-t]

Are you formatting the query in any way, passing it some values? Please show the exact method oneOrNone, with values as you execute it.

Also, do add the actual query executed, as reported by event [query] or [pg-monitor], for comparison.

[UJPrasad]

o@vitaly-t
No, I'm not formatting the query any further,

The actual query which I executed is:

db.oneOrNone(`
with cte as (select json_build_object(
  upper(
    regexp_replace(
      replace(policies.policy_name, ' ', '_'), 
   '[^\w]+','','g')
  ), policies.*
) as c from policies)
select jsonb_object_agg(t.k, t.v) as policies
from cte, json_each(cte.c) as t(k,v)
`)

And it is the same exact query which I've used in PSQL CLI.

with cte as (select json_build_object(
  upper(
    regexp_replace(
      replace(policies.policy_name, ' ', '_'), 
   '[^\w]+','','g')
  ), policies.*
) as c from policies)
select jsonb_object_agg(t.k, t.v) as policies
from cte, json_each(cte.c) as t(k,v)

The results were as given above in expected behavior/ actual behavior
Do I need to give any more details to the quickest reproduction of the problem?

Thanks!

[vitaly-t]

You can try and monitor the actual query that's being executed by the server. It is also possible that [underlying driver] is the culprit. But as far as this library goes, nothing seems wrong.

[UJPrasad]

Got it! The actual query being executed, reported by event query was:

with cte as (select json_build_object(
  upper(
    regexp_replace(
      replace(policies.policy_name, ' ', '_'), 
   '[^w]+','','g')
  ), policies.*
) as c from policies)
select jsonb_object_agg(t.k, t.v) as policies
from cte, json_each(cte.c) as t(k,v)

which is removing the escape character in regexp expression. Added an extra escape character to fix this :)

Closing this issue.

[vitaly-t]

@UJPrasad You should keep any large SQL inside SQL files, to avoid this kind of problems.

[UJPrasad]

@vitaly-t that's a great feature which I haven't explored. How do I append additional query to query which is already in query file?

Example: If I have a select query in the query file (which is quite complex), how do I attach the conditional part of the query which is generated through some JS? (having a complex conditional part which can only be generated on the go with JS). Have any suggestions for me?

[vitaly-t]

@UJPrasad This question should help.