How to get all values from json

Off-Topic
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”:“BlaBla · GitHub”,
“closedAt”:“2019-07-11T18:38:15Z”,
“state”:“MERGED”,
“createdAt”:“2019-07-11T18:35:22Z”},
{
“title”:“Fix part2”,
“url”:“BlaBla · GitHub”,
“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?

5

You probably could use pipelines to stored procedures, load the one row of JSON, then use JSON_TO_ARRAY on the contents and insert rows separately into a target table (one row for each array element).