How to Find the Size of an Array in Postgresql

Determine size of input array plpgsql

Use array_length:

SELECT array_length( ARRAY[1,2,3], 1 );

The 2nd parameter is the dimension you're interested in. Most arrays are 1-dimensional, so 1 is the right value in most cases.

If you're in fact looking for the length of all the strings in a text array, use array_to_string with length:

SELECT length(array_to_string( ARRAY['a','bb','ccc'], '' ));                                                                                                         

BTW, the article you linked to is 12 years old and appears completely obsolete.

How to get an array in postgres where the array size is greater than 1

Use Having clause to filter the groups which is having more than fkey

SELECT val, array_agg(fkey)
FROM mytable
GROUP BY val
Having Count(fkey) > 1

Finding array length in postgres

That should work:

select array_upper ( value, 1 ) from table_name_here;

Note: 'VALUE' is reserved keyword in SQL, so it is not recommended to use it as a column name. See: http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

Get size of an array of strings in PSQL

You want to use the jsonb_array_length() function for jsonb data. The array_length() function is for native arrays such as text[].

with invars as (
  select '{
    "outer": [
        {
            "keys": {
                "id": 5
            },
            "name": "Joe Bloggs",
            "age": "16",
            "new_rels": [
                "a6h922ao-621y-230p-52bk-t6i84rr3vo6g"
            ],
            "old_rels": [
                "9c8b67bf-871e-4004-88be-9a68dae3a86f",
                "e6a15929-4aab-4af6-903a-8f8c09bef572"
            ],
            "s_id": 1
        }
    ],
    "total": 0
}'::jsonb as r
)
select jsonb_array_length(r->'outer'->0->'new_rels'), 
       jsonb_array_length(r->'outer'->0->'old_rels') 
  from invars;

 jsonb_array_length | jsonb_array_length 
--------------------+--------------------
                  1 |                  2
(1 row)

Also, you had an extra comma after the total key.

PostgresQL: Find array length of output from ARRAY_AGG()

the function array_length(anyarray, int) require two elements, array and dimension for example:

Select array_length(array[1,2,3], 1);

Result:
3

How to select size of json array in postgres?

The right hand parameter for the -> operator should be a text value, not a json value. So the cast 'InnerArray'::json is not needed to begin with.

But it's also the reason for your error, because 'InnerArray' isn't a valid JSON value, so you can't cast it to one.

Use:

SELECT json_array_length("Data"::json -> 'InnerArray') AS lengtha

Btw: if you do store JSON values, your column should be defined as jsonb (or at least json), rather than using text (or varchar) and casting it everytime you want to use a JSON function.

How to get the dimensionality of an ARRAY column?

For starters, the dimensionality of an array is not reflected in the data type in Postgres. The syntax integer[][] is tolerated, but it's really just integer[] internally.

Read the manual here.

This means that dimensions can vary within the same array type (the same table column).

To get actual dimensions of a particular array value:

SELECT array_dims(my_arr);  -- [1:2][1:3]

Or to just get the number of dimensions:

SELECT array_ndims(my_arr);  -- 2

There are more array functions for similar needs. See table of array functions in the manual.

Related:

• Use string[][] with ngpsql

If you need to enforce particular dimensions in a column, add a CHECK constraint. To enforce 2-dimensional arrays:

ALTER TABLE tbl ADD CONSTRAINT tbl_arr_col_must_have_2_dims
CHECK (array_ndims(arr_col) = 2);

PostgreSQL - How to find length of sub array

PostgreSQL's arrays are not arrays of arrays like in some other languages. In PostgreSQL, there are only multidimensional arrays, and it is not easy to take slice. So your question in Postgres has not sense, just there are not sub arrays.

You can take info about the dimensions of the array:

(2022-09-09 18:45:44) postgres=# select array_dims(ARRAY[[[1,2,3],[1,2,3]],[[2,3,4],[1,2,3]]]);
┌─────────────────┐
│   array_dims    │
╞═════════════════╡
│ [1:2][1:2][1:3] │
└─────────────────┘
(1 row)

or you can see the sizes

(2022-09-09 18:46:30) postgres=# select array_length(ARRAY[[[1,2,3],[1,2,3]],[[2,3,4],[1,2,3]]],1);
┌──────────────┐
│ array_length │
╞══════════════╡
│            2 │
└──────────────┘
(1 row)

(2022-09-09 18:47:54) postgres=# select array_length(ARRAY[[[1,2,3],[1,2,3]],[[2,3,4],[1,2,3]]],2);
┌──────────────┐
│ array_length │
╞══════════════╡
│            2 │
└──────────────┘
(1 row)

(2022-09-09 18:47:56) postgres=# select array_length(ARRAY[[[1,2,3],[1,2,3]],[[2,3,4],[1,2,3]]],3);
┌──────────────┐
│ array_length │
╞══════════════╡
│            3 │
└──────────────┘
(1 row)

When you use not enough indexes, then Postgres try to make some slices, but when you don't specify slice correctly, then you get NULL value (this is not the array), and then there cannot be used function array_length:

-- not fully specified cell in array
(2022-09-09 18:55:51) postgres=# select (array[[1,2,3],[4,5,6]])[1];
┌───────┐
│ array │
╞═══════╡
│     ∅ │
└───────┘
(1 row)

(2022-09-09 18:56:14) postgres=# select (array[[1,2,3],[4,5,6]])[1:1];
┌───────────┐
│   array   │
╞═══════════╡
│ {{1,2,3}} │
└───────────┘
(1 row)

(2022-09-09 18:56:21) postgres=# select (array[[1,2,3],[4,5,6]])[1:2];
┌───────────────────┐
│       array       │
╞═══════════════════╡
│ {{1,2,3},{4,5,6}} │
└───────────────────┘
(1 row)

---

Related Topics