Get JSON Array from JSON Object and Count Number of Objects

Question

I have a column that contains some data like this:

{
  "activity_goal": 200,
  "members": [
    {
      "json": "data"
    },
    {
      "HAHA": "HAHA"
    },
    {
      "HAHA": "HAHA"
    }
  ],
  "name": "Hunters Team v3",
  "total_activity": "0",
  "revenue_goal": 200,
  "total_active_days": "0",
  "total_raised": 300
}

I am using cast(team_data -> 'members' as jsonb) to get the "Members" JSON array, which gives me a column like this:

[
  {
    "json": "data"
  },
  {
    "HAHA": "HAHA"
  },
  {
    "HAHA": "HAHA"
  }
]

I am using array_length(cast(team_data -> 'members' as jsonb), 1) to pull a column with the number of Members that exist in the list. When I do this, I am given this error: function array_length(jsonb, integer) does not exist Note: I have also tried casting as "json" instead of "jsonb"

I am following this documentation. What am I doing wrong?

ggordon · Accepted Answer · 2021-05-11 14:19:28Z

1

Use the JSON functions when working with json such as json_array_length

select json_array_length(team_data -> 'members') from mytable

answered May 11, 2021 at 14:19

ggordon

10.1k2 gold badges18 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

Get JSON Array from JSON Object and Count Number of Objects

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