Question 88999 in PostgreSQL Question posted in
The official documentation can be found here.

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

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"}]');

  1. 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;

  2. Output:

    {"loadShortCut_AutoText":"both"} both

Tags:

3

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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}]|       |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search