I have a json array in a column with datatype(text) [{"address":"127.0.0.1","family":2,"fd":3}, {"address":"127.0.0.1","family":2,"fd":5}]

I want write a query so that it shows only the fd fields from the json array.How can I do this?.

CodePudding user response：

select json_array_elements(your_column::json)->>'fd' from your_table;. This will first cast your elements to a json and then unwraps the array and then select the the 'fd' field. If this doesn't work for you then enhance your question with exact details of the table and some sample data and expected output. This query may not be performant depending upon the size of the table. Also, it is better to store json data as jsonb data type in postgres from a performance standpoint.