Select in Json data

Question

With this select example, I get the Account information I have inside my json:

 select data::json#>>'{account}' as codigo,
                  assinatura
           from assinante ce
           where assinatura in ('XXX')

I can get the data I need from the json that’s like this:

"id":00003,
"name":"TESTE",
"account":"000000023"

Now, I want to get the code that’s inside my json like this:

"assinante":{ 
         "codigo":"0000025",
         "documento":"00000005"
 }

How do I get the same way I get the Account, but it’s "out," get my code that’s inside the subscriber?

Answer 1

With this select you get everything that is inside subscriber:

SELECT
   #suaColunaJson -> 'assinante' as assinante.
FROM
   assinante

With this other, you take everything that’s in code, inside the subscriber

SELECT
   #suaColunaJson -> 'assinante' ->> 'codigo' as codigo_assinante.
FROM
   assinante