Re: "Explodir" um JSON Array em recordset

Bom dia!

create table exemplo (jsonarray jsonb);

insert into exemplo values (
'[{"usuario":"pedrobase","data":"08\/10\/2018
14:02:34","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:38","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:38","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:39","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:39","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:48","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
14:02:50","comentario":""}]');

*select * from jsonb_to_recordset((select jsonarray from exemplo)) as
dados(usuario varchar(40), data varchar(40), comentario varchar(40));*

Boa sorte

Douglas Rauber

Em qua, 24 de out de 2018 às 11:39, Willian Jhonnes <
willianjhonnes(at)gmail(dot)com> escreveu:

> Bom dia a todos.
>
> Em certa tabela, tenho um campo jsonb que contem o seguinte array:
>
> [{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:34","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:38","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:38","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:39","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:39","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:48","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:49","comentario":""},{"usuario":"pedrobase","data":"08\/10\/2018
> 14:02:50","comentario":""}]
>
> Preciso explodí-lo em um recordset como este:
>
> usuario |data |comentario
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:34|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:38|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:38|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:39|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:39|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:48|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:49|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:49|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:49|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:49|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:49|
> ----------+-------------------+----------
> pedrobase |08/10/2018 14:02:50|
>
> Em uma consulta à documentação [
> https://www.postgresql.org/docs/current/static/functions-json.html] não
> encontrei nada que realmente me ajudasse nesta questão.
>
> Qual a melhor forma de obter o resultado demonstrado no exemplo?
>
> ---------------------------------------------------
> Att.:
> Willian Jhonnes L. dos Santos
> Object/Free Pascal Analyst/Developer
> PHP Analyst/Developer
> willianjhonnes(at)gmail(dot)com
> ---------------------------------------------------
> Only those who will risk going too far can possibly find out how far one
> can go. - T. S. Eliot
> Computing Engineering - PUC-PR
> Be free. Use Linux.
> São José dos Pinhais GNU/Linux User Group
> Linux user number 449753
> ---------------------------------------------------
> Powered by Slackware Linux 14.2 64 bits
> Kernel 4.4.38-x86_64-iCore
> ---------------------------------------------------
>