🐛 Bug: JSON_EXTRACT in formula with a special character fails

[imajus]

Please confirm that the bug report does not already exist

• I confirm there is no existing issue for this bug.

Steps to reproduce

I am having an issue with a formula field that uses the 'JSON_EXTRACT' function. When I try to save the field, the system does not allow this and instead displays the following error: "Formula error: unsupported Unicode escape sequence".

My formula is quite simple:

JSON_EXTRACT({Metadata}, ".name")

Here's the backend log output:

error: select CASE WHEN (("p8ddrqb7p1oiybw"."mytable"."Metadata")::text )::jsonb IS NOT NULL THEN jsonb_path_query_first((("p8ddrqb7p1oiybw"."mytable"."Metadata")::text )::jsonb, CONCAT('$', '.name' )::jsonpath) ELSE NULL END as "__dry_run_alias" from "p8ddrqb7p1oiybw"."mytable" - unsupported Unicode escape sequence
at Parser.parseErrorMessage (/usr/src/app/node_modules/pg-protocol/dist/parser.js:283:98)
at Parser.handlePacket (/usr/src/app/node_modules/pg-protocol/dist/parser.js:122:29)
at Parser.parse (/usr/src/app/node_modules/pg-protocol/dist/parser.js:35:38)
at Socket.<anonymous> (/usr/src/app/node_modules/pg-protocol/dist/index.js:11:42)
at Socket.emit (node:events:518:28)
at Socket.emit (node:domain:489:12)
at addChunk (node:internal/streams/readable:561:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
at Readable.push (node:internal/streams/readable:392:5)
at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
length: 214,
severity: 'ERROR',
code: '22P05',
detail: '\\u0000 cannot be converted to text.',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: 'JSON data, line 1: ..."string","default":"","placeholder":"e.g., \\u0000...',
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'jsonfuncs.c',
line: '645',
routine: 'json_errsave_error'
}
NcBaseErrorv2: Formula error: unsupported Unicode escape sequence
at NcError.formulaError (/usr/src/app/docker/main.js:1553:42460)
at v (/usr/src/app/docker/main.js:272:30345)
at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
at async ColumnsService.columnUpdate (/usr/src/app/docker/main.js:1752:379303)
at async ColumnsController.columnUpdate (/usr/src/app/docker/main.js:184:38580) {
error: 'FORMULA_ERROR',
code: 400,
details: undefined
}

The field is not new; it used to work. However, since a record containing that special character was added to the JSON content, the field has stopped working. Now, it fails to render values for all records at once because of this.

Desired Behavior

The unsupported characters should either be escaped or removed from the data before it is written to the database.

Project Details

Node: v22.14.0
Arch: x64
Platform: linux
Docker: true
RootDB: pg
PackageVersion: 0.262.5

Attachments

[dstala]

Node: v22.19.0
Arch: arm64
Platform: linux
Docker: true
RootDB: pg
PackageVersion: 0.264.9

I tried extracting info from following JSON -- .name & .address.city : both worked fine

{
  "name": "John Doe",
  "age": 30,
  "email": "john.doe@example.com",
  "isActive": true,
  "roles": ["admin", "editor"],
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "zipCode": "10001"
  }
}

@imajus
Can you please share your {metadata} content? Also- please check if the issue still persists on 0.264.9 release.

[fendy3002]

steps to reproduce:

• Create table 1
• Add JSON field to table 1
• Set JSON field value to:

{
  "name": "\u0000"
}

• Add formula with JSON_EXTRACT({JSON}, ".name")

[ashwintemkar]

Hi guys I can go ahead and take up this issue

[ashwintemkar]

Hi so I observed that the existing mapper first forces the JSON column through ::text . Once ::text sees \u0000 , the query fails and the regex never runs.

so I am thinking of an approach where we can compile the original expression, strip the trailing text cast if extra, and feed the raw JSON straight into jsonb_path_query_first so postgres will never hit the \u0000 trap where the query will remain customizable.

please let me know if I can go ahead with this approach. I find this safer as I don't want to inject stringified SQL fragments, which will risk the malformed / abnormal queries.

[fendy3002]

it should be already fixed with #12391

[ashwintemkar]

it should be already fixed with #12391

hey I think in #12391 trying to fix it with regexp_replace would cause problems ahead, because it will leave the ::text cast in place

[fendy3002]

hi @ashwintemkar , if you can confirm it please open a new issue. If you can provide a pr then please link to the new issue.