Postgres text sorting: white space is lesser than end of string in json strings - bug or working as intended?

Question

If I perform this simple query

SELECT * 
FROM (
  SELECT 'abc' AS name 
  UNION ALL 
  SELECT 'abc d' AS name
) AS test 
ORDER BY name;

I get (as expected) the following result:

name
-------
 abc
 abc d
(2 rows)

i.e. the space character of string abc d is considered greater than the end of string of abc.

Till here everything is fine.

Next, if I try:

SELECT * FROM (
  SELECT '{"name":"AbC"}'::JSON AS json
  UNION ALL
  SELECT '{"name":"aBc D"}'::JSON AS json
) AS test ORDER BY LOWER((json -> 'name')::TEXT);

I get:

      json
------------------
 {"name":"aBc D"}
 {"name":"AbC"}
(2 rows)

this time the space character of string abc d is considered lesser than the end of string of abc!

I also tried with:

SELECT * 
FROM (
  SELECT 'abc'::TEXT AS name 
  UNION ALL SELECT 'abc d'::TEXT AS name
) AS test 
ORDER BY name;

SELECT * 
FROM (
  SELECT 'abc' AS name 
  UNION ALL 
  SELECT 'abc d' AS name
) AS test 
ORDER BY LOWER(name);

SELECT * 
FROM (
  SELECT 'abc'::TEXT AS name 
  UNION ALL 
  SELECT 'abc d'::TEXT AS name
) AS test 
ORDER BY LOWER(name);

and all of them give the expected result.

I also tried:

SELECT * FROM (
  SELECT '{"name":"abc"}'::JSON AS json
  UNION ALL
  SELECT '{"name":"abc d"}'::JSON AS json
) AS test 
ORDER BY (json -> 'name')::TEXT;

which considers the space character lesser than the end of string as well.

It seems that the unexpected behavior is originated by the conversion of a string property of a JSON field to a (any?) postgres string type. I tried to search in PostrgeSQL documentation for a reason of this phenomenon but I wasn't able to.

Could somebody point me the documentation speaking about this? Or should we consider this a bug?

I think the horse is right, does it convert jsonb to text first before sorting? select pg_typeof(('{"name": "abc"}'::jsonb)->'name') is still jsonb at this stage — user935712063
– user935712063, Commented Feb 13, 2023 at 10:49
Yes @MâttFrëëman , it is. So now the question is: which is the right convertion function? With right conversion function I mean the function which also excludes the double quotes — Daniele Ricci
– Daniele Ricci, Commented Feb 13, 2023 at 10:58
it uses compareJsonbContainers which calls compareJsonbScalarValue which then compares the string - the quoted string in this case rather than the unwrapped one github.com/postgres/postgres/blob/… - interesting. But alas the answer is ->> or jsonb_extract_path_text if you want to compare without quotes. — user935712063
– user935712063, Commented Feb 13, 2023 at 11:03

user330315 · Accepted Answer · 2023-02-13 11:09:16Z

Use the ->> operator to extract the sort value as text directly.

SELECT * 
FROM (
  SELECT '{"name":"AbC"}'::JSON AS json
  UNION ALL
  SELECT '{"name":"aBc D"}'::JSON AS json
) AS test 
ORDER BY LOWER(json ->> 'name');

Casting a JSON value to text does something different.

Your attempt:

SELECT * 
FROM (
  SELECT '{"name":"abc"}'::JSON AS json
  UNION ALL
  SELECT '{"name":"abc d"}'::JSON AS json
) AS test 
ORDER BY lower((json -> 'name')::TEXT);

Is equivalent to this setup:

SELECT * 
FROM (
  SELECT '"abc"' AS name 
  UNION ALL 
  SELECT '"abc d"' AS name
) AS test 
ORDER BY name;

I don't know why the double quotes change the sort order though. It probably has something to do with the underlying collation. Maybe it would be possible to create an ICU collation that sorts quoted strings the same ways as unquoted strings.

Collectives™ on Stack Overflow

Postgres text sorting: white space is lesser than end of string in json strings - bug or working as intended?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related