How to retrieve value based on Key from JSON string in PosgresSql

While working on JSON string which is stored in a DB table column as:

lets say table name is t1 and column name is data:

data column stores json string as:

[{"K":"V"}]

Below is my query to get the Value based on Key:

select Value ->> 'Value'
from json_array_elements(select data from t1) Key
where Key->>'Key'='K';

on execution, it gives me syntax error.

Tried all the possible way to fix this but finally need help.

----Edit This is how I got the required result------

1. create a table:

   CREATE TABLE t1 (
   id int,
   data json
   );

2. Insert data into t1 table:

insert into t1 values

(1,'[{"K":"V"}]'),
(2,'[{"loadShortCut_AutoText":"both"}]'),
(3,'[{"P":"R"}]');

3. Below query to get the output:

   select row, row->>'loadShortCut_AutoText' as value
   from t1, json_array_elements (t1.data) as row
   WHERE t1.id=2;

4. Output:

   {"loadShortCut_AutoText":"both"} both

You can Try this :

select row->>'K' as value
from t1, json_array_elements (data) as row
WHERE row->>'K' is not null

This will return data only if the key 'K' exists

Demo here

json_array_elements() returns one row per array element which again is a JSON value. To access the value from that element you need to use ->> 'K'

A select statement can not be passed as a parameter. You need to put the source table into the "main" FROM clause:

select e.item ->> 'K' as value
from t1 
   cross join json_array_elements(t1.data) as e(item)

Alternatively you may use jsonb_path_query_array to query direct the json array (cast of the json column to jsonb is required)

select
id, data,
(jsonb_path_query_array(t1.data::jsonb, '$[*]?(exists (@."K"))'::jsonpath) -> 0) #>> '{K}'::text[] AS k_value
from t1

The JSON path $[*]?(exists (@."K")) select all array element that contains the key "K" than takes the first such -> 0 and extracts the value of it #>> '{K}'::text[].

The advantage of this approach is that it works also for JSON arrays with duplicit data (id=4) and null values (id=6) as illustrated on the sample output

id|data                   |k_value|
--+-----------------------+-------+
 1|[{"K":"V"}]            |V      |
 2|[{"K":"V"},{"A":"B"}]  |V      |
 3|[{"Z":"V"},{"A":"B"}]  |       |
 4|[{"K":"V"},{"K":"B"}]  |V      |
 5|[{"Z":"V"},{"K":"B"}]  |B      |
 6|[{"Z":"V"},{"K": null}]|       |