PostgreSQL negative "-0.0" in jsonb[] field

Question

I have an "issue" (probably coming from my understanding of the json, jsonb and jsonb[] types) which is the following:

My team and I insert 3D coordinates records into a table whose column type is jsonb[].

The table contains an id column, that is based on the text representation of the records. With that said, when I select some data from the table, all the records containing coordinates whose values where equal to -0.0 (bit sign on), the data I get back does not contain the minus sign. -0.0 -> 0.0.

I am aware of the IEEE754 RFC on floating point numbers (and the comparison section), I was wondering, how does Postgres treat the elements inside the jsonb array? Is there a conversion/casting done on the fly when inserting the data. Is there a way to deactivate such behaviour?

Minimal reproducible code:

CREATE TABLE IF NOT EXISTS "my_test_table" (
    coordinates jsonb[]
);

TRUNCATE TABLE "my_test_table";


INSERT INTO 
    "public"."my_test_table" ("coordinates")
VALUES (
ARRAY[jsonb_build_array(-0.0, 0.0,0.0)]::jsonb[]
);


select * from my_test_table;

The output is:

{"[0.0, 0.0, 0.0]"}

when one expects:

{"[-0.0, 0.0, 0.0]"}

Answer 1

PostgreSQL stores numbers as numeric in jsonb. See this code in src/backend/utils/adt/jsonb.c:

static JsonParseErrorType
jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype)
{
    [...]

    switch (tokentype)
    {
        [...]
        case JSON_TOKEN_NUMBER:

            /*
             * No need to check size of numeric values, because maximum
             * numeric size is well below the JsonbValue restriction
             */
            Assert(token != NULL);
            v.type = jbvNumeric;
            if (!DirectInputFunctionCallSafe(numeric_in, token,
                                             InvalidOid, -1,
                                             _state->escontext,
                                             &numd))
                return JSON_SEM_ACTION_FAILED;
            v.val.numeric = DatumGetNumeric(numd);
            break;
        [...]

Data type numeric has no notion of “negative zero”:

SELECT -0::float8 AS float8, -0::numeric AS numeric;

 float8 │ numeric 
════════╪═════════
     -0 │       0
(1 row)

numeric is stored as binary coded decimal.

Answer 2

Addressing the other question:

Is there a way to deactivate such behaviour?

Yes, there is. At a price of everything jsonb offers over json: demo at db<>fiddle

select a::json "json",
       a::jsonb "jsonb" 
from (values ('[-0.0,0.0,0.0]'))_(a);

json	jsonb
[-0.0,0.0,0.0]	[0.0, 0.0, 0.0]

create table test as 
select a::json "json",
       a::jsonb "jsonb" 
from (values ('[-0.0,1.1,2.2]'))_(a);

select 'json',
       json->>0 "->>",
       json->0 "->",
       pg_typeof(json->0),
       json->0 is json scalar AS "is json scalar",
       json_typeof(json->0)
from test
union all
select 'jsonb',
       jsonb->>0,
       (jsonb->0)::json,
       pg_typeof(jsonb->0),
       jsonb->0 is json scalar,
       jsonb_typeof(jsonb->0)
from test;

?column?	->>	->	pg_typeof	is json scalar	json_typeof
json	-0.0	-0.0	json	t	number
jsonb	0.0	0.0	jsonb	t	number

If you need the jsonb functionality and the fields to be of number type while holding onto the bit sign for some other logic, you can save it under a separate, appropriately named key, or another array of boolean values that hold corresponding "source" bit signs of your coordinates: demo2

TRUNCATE TABLE "my_test_table";
alter table "public"."my_test_table" add column "source_bit_signs" jsonb[];

INSERT INTO "public"."my_test_table" ("coordinates","source_bit_signs")
select ARRAY[jsonb_build_array(x::numeric,y::numeric,z::numeric)]::jsonb[],
       ARRAY[jsonb_build_array(x~'-.*',y~'-.*',z~'-.*')]::jsonb[]
from (values ('-0.0', '0.0','0.0'))_(x,y,z)
returning *;

coordinates	source_bit_signs
{"[0.0, 0.0, 0.0]"}	{"[true, false, false]"}

That of course forces you to bind those coordinates as string types instead of numeric types.

You can also not use number-type fields, and save the coordinates as string fields in jsonb. The size, indexing and JSONPath processing won't be the same, but other than that, most in-db type mapping on json/jsonb output goes through text type first anyways, before you get to interact with the field for the purpose of a query.