CODEWITHSUNDEEP
sqljsonpostgresql
Hunter Casillas
Hunter Casillas

Reputation: 33

Get JSON Array from JSON Object and Count Number of Objects

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?

Upvotes: 0

Views: 296

Answers (1)

ggordon
ggordon

Reputation: 10035

Use the JSON functions when working with json such as json_array_length

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

Upvotes: 1

Related Questions