How to select size of json array in postgres?

Question

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.

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Oct 14, 2020 at 9:01
Yes, I am aware of that, thank you, however I am probably not going to rewrite existing product with hundreds of tables. — Jiří Herník
– Jiří Herník, Commented Feb 28, 2023 at 12:31

score 2 · Accepted Answer · 2020-10-14 09:10:12Z

2

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.

edited Oct 14, 2020 at 9:10

answered Oct 14, 2020 at 8:59

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Jiří Herník Over a year ago

Wow, I thought I tried that one a thousand times.Also, I have my data stored as json datatype, but it's defined by 3rd party product, so not much what I can change anyways. Thanks.

user330315 Over a year ago

@JiříHerník: if the column is defined as json you can get rid of the cast.

Bjarni Ragnarsson · Accepted Answer · 2020-10-14 08:53:57Z

0

You are missing a }. Try this:

select json_array_length('{"$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"}]}'::json->'InnerArray') as lengtha

Best regards,
Bjarni

answered Oct 14, 2020 at 8:53

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

1 Comment

Jiří Herník Over a year ago

Thanks, I fixed it, but the point is, that I need to select it from DB field, not inline.

Collectives™ on Stack Overflow

How to select size of json array in postgres?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related