Count json occurrence inside array SQL

Question

In my table, i've got a column where I save an array of jsons as a string, like this:

[
  {
  "id":0,
  "stops":[
     {
        "Id":0,
        "field1":"data1",
        "field2":"data2"
     }
  ],
  "time":"01:00:00"
  },

  {
  "id":2,
  "stops":[
     {
        "Id":0,
        "field1":"data1",
        "field2":"data2"
     }
  ],
  "time":"01:00:00"
  },
]

I want to count the number of jsons in this array. The database is in PostgreSQL 9.6.1.

In your example, is the expected output 2?4? Which database? (Mysql, sql server, etc?) — user5051310
– user5051310, Commented May 4, 2018 at 21:37
Sorry, the database is in PostgreSQL 9.6.1, and the output in this case, is 2. — Marcus S.F.
– Marcus S.F., Commented May 7, 2018 at 13:34

David Silva · Accepted Answer · 2018-05-07 20:02:00Z

2

You can use the json_array_length() function to count, but if your column is of text type you will need to cast the column to become a json type, as follows:

json_array_length(cast(your_table.column_name as json))

Also, if you need to access your "stops" field, you could use json_array_elements() and do it like this:

json_array_elements( cast(your_table.column_name as json))->'Stops'

And to access even further, like fields inside "stop", you can just use the same function again:

json_array_elements( (json_array_elements( cast(your_table.column_name as json)))->'Stops')->'field1'

Hope I could help.

edited May 7, 2018 at 20:02

answered May 7, 2018 at 19:56

David Silva

1005 bronze badges

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

Collectives™ on Stack Overflow

Count json occurrence inside array SQL

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