How to get all values from json


#1

Hello, I try to make a pipeline s3 in json format, when start pipeline I join the first element in a single row, when 3 rows should appear with null

CREATE PIPELINE tst
AS LOAD DATA S3 ‘bucket/test/’
CONFIG ‘{“region”: “us-west-2”}’
CREDENTIALS ‘{“aws_access_key_id”: “”, “aws_secret_access_key”: “”}’
INTO TABLE tst
FORMAT JSON
(
project <- projects::project_name DEFAULT NULL,
title <- projects::elements::title DEFAULT NULL,
url <- projects::elements::url DEFAULT NULL,
closedAt <- projects::elements::closedAt DEFAULT NULL,
state <- projects::elements::state DEFAULT NULL,
createdAt <- projects::elements::createdAt DEFAULT NULL
)

JSON:

{“projects”:[
{
“project_name”:“candys”,
“elements”:
[]
},
{
“project_name”:“R12”,
“elements”:[
{
“title”:“Fixes profile”,
“url”:“https://github.com/blabla”,
“closedAt”:“2019-07-11T18:38:15Z”,
“state”:“MERGED”,
“createdAt”:“2019-07-11T18:35:22Z”},
{
“title”:“Fix part2”,
“url”:“https://github.com/BLABLA”,
“closedAt”:“2019-07-11T21:46:19Z”,
“state”:“MERGED”,
“createdAt”:“2019-07-11T21:44:29Z”}]},
{
“project_name”:“ProxyService”,
“elements”:[]
}
]
}

THX


#2

Looks to me like projects is an array, so

projects::project_name

is not meaningful.


#3

This function, JSON_TO_ARRAY(), might be useful to you at some point:

It turns a JSON array into an MPSQL array of JSON values.


#4

Hi thanks for the answer, within the json 3 rows are inside, I think that each json is considered as 1 row and that is why it does not read all the information because the names of the attributes are duplicated 3 times
I can also use JSON_TO_ARRAY from the pipeline?