0

i have this table :

enter image description here

And i want display all the names in the table like this :

enter image description here

Also i want to list the number of gender each genre : Desired output:

enter image description here

0

According to the documentation, the #> operator comes in useful:

-- name and genre of people (1 row per person)
select
    json_array_elements(
      t.test#>'{clients, info}'
        )::json
    ->>'nom' as nom,
    json_array_elements(
      t.test#>'{clients, info}'
        )::json
    ->>'genre' as genre
from my_table t
-- number of people, grouped by gender (1 row per gender)
select
      json_array_elements(
         t.test#>'{clients, info}'
           )::json
           ->>'genre' as genre,
      count (t.*) as count
from my_table t
group by 1

SqlFiddle

  • THank u uu for your response but i have an error : ERROR: ERREUR: ne peut pas appeler json_array_elements sur un type non tableau SQL state: 22023 – Maliari Mohammed May 29 at 0:31
  • Odd ; I made a fiddle for easier editing. Maybe try adding some parentheses around t.test#>'{clients, info}')::json ? – mcadorel May 29 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.