How to select size of json array in postgres?

I’ve been fighting this for a long time, but I am not able to construct a query, which would select (and preferably also order by) a count of items in an array.

I do have table data like this:

ID    Data
1     {"$id": "1", "InnerArray": [{"$id": "1", "Timestamp": "2020-06-18T09:43:19.4873323+01:00"}, {"$id": "3", "Timestamp": "2020-06-19T08:25:35.7768657+00:00"}]}
etc...

And what did I try…

SELECT JSON_ARRAY_LENGTH("Data" ->'InnerArray'::json) AS lengtha
FROM "mystorage"

also

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

But it sais

SQL Error [22P02]: ERROR: invalid input syntax for type json
  Detail: Token "InnerArray" is invalid.
  Position: 49
  Where: JSON data, line 1: InnerArray

I’ve tried a lot of different formats but coming from the MS SQL world, my understanding of PostgreSQL seems a bit limited.

Answer

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.