I need to convert some queries written for MariaDB to PostgreSQL syntax. Unfortunately they use the JSON_LENGTH function of MariaDB and im struggling to find an alternative with PostgreSQL.

For clarification:

JSON_LENGTH counts the number of 'entries' on the root level of a JSON object/array in MariaDB:

SELECT
    JSON_LENGTH('{"test": 123}') as test1, -- 1
    JSON_LENGTH('"123"') as test2, -- 1
    JSON_LENGTH('123') as test3, -- 1 
    JSON_LENGTH('[]') as test4, -- 0
    JSON_LENGTH('[123]') as test5, -- 1
    JSON_LENGTH('[123, 456]') as test6, -- 2
    JSON_LENGTH('[123, {"test": 123}]') as test7; -- 2

Partial solutions in PostgreSQL I came up with:

select
    json_array_length('[]'::json) as test1, -- 0
    json_array_length('["a"]'::json) as test2, -- 1
    length(json_object_keys('{"a": "b"}'::json)) as test3; -- 1

• json_array_length is not allowed for JSON objects
• json_object_keys is not allowed for JSON arrays

Unfortunately I can't manage to combine the two methods I figured out for PostgreSQL:

I tried to use CASE WHEN:

select
    case
        when json_typeof('["a"]'::json) = 'array' then json_array_length('["a"]'::json)
        when json_typeof('["a"]'::json) = 'object' then length(json_object_keys('["a"]'::json))
        else 0
    end;

Error:

[0A000] ERROR: set-returning functions are not allowed in CASE

-> json_object_keys is the bad guy here

I tried IF ELSE:

select
    IF ('array' = json_typeof('["a"]'::json)) THEN json_array_length('["a"]'::json)
    ELEIF (json_typeof('["a"]'::json) = 'object') THEN length(json_object_keys('["a"]'::json))
    ELSE 0;

Error:

[42601] ERROR: syntax error at or near "THEN"

I can imagine I have an error in my IF ELSE statement, but I'm unable to figure it out.

Is there any way to replicate MariaDB's JSON_LENGTH behavior with PostgreSQL?

CodePudding user response：

You need to count the number of rows returned by jsonb_object_keys():

This:

with data (input) as (
values
    ('{"test": 123}'::jsonb),
    ('"123"'),
    ('123'),
    ('[]'),
    ('[123]'),
    ('[123, 456]'),
    ('[123, {"test": 123}]')
)
select input, 
    case jsonb_typeof(input)
        when 'array' then jsonb_array_length(input)
        when 'object' then (select count(*) from jsonb_object_keys(input))
        when 'string' then 1
        else 0
    end
from data    

returns:

input                | case
--------------------- -----
{"test": 123}        |    1
"123"                |    1
123                  |    0
[]                   |    0
[123]                |    1
[123, 456]           |    2
[123, {"test": 123}] |    2

Of course this can easily be put into a function:

create or replace function my_json_length(p_input jsonb) 
  returns int
as
$$
select case jsonb_typeof(p_input)
         when 'array' then jsonb_array_length(p_input)
         when 'object' then (select count(*) from jsonb_object_keys(p_input))
         when 'string' then 1
         else 0
       end;
$$
language sql
immutable;    

I would not name it json_length() to avoid any clashes in case Postgres decides to implement such a function in the future (although I am not aware of such a function in the SQL standard).

Note that jsonb is the recommended data type to store JSON values in Postgres.