How do we extract json first array element in postgresql

Question

Tablename:-table_data_set, Columnname:-info

data format:

{
   "al_uri":"https://al/ma/np_6101",
   "parameters":[
      {
         "file_name":"QPR.AP6101_%Y%m%d",
         "input_format":"fixed",
         "output_format":"parquet",
         "delimiter":"None",
         "extension":".dat",
         "header":0,
         "footer":0,
         "date_pattern":"%Y%m%d%H%M%S",
         "type":"incremental"
      }
   ]
}

How to extract QPR.AP6101_%Y%m%d from the array. Tried below query, but not returning data.

select name,info ->> 'al_uri' as URL,info -> 'parameters' ->> 'file_name' as filename 
from table_data_set;

user330315 · Accepted Answer · 2020-05-26 05:33:19Z

5

The -> operator also has a version where the right hand argument is an integer, that will select the corresponding array element.

So you want:

select name,
       info ->> 'al_uri' as URL,
       info -> 'parameters' -> 0 ->> 'file_name' as filename 
from table_data_set;

info -> 'parameters' -> 0 picks the first array elements from parameters

A slightly shorter version is the #>> operator which accepts a path to an element:

input #>> '{parameters,0,file_name}' as file_name

answered May 26, 2020 at 5:33

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do we extract json first array element in postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related