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

Sql query to find value of key in json array which contains given string – Postgresql

column name "note" – json type

data in one cell of the column is written in the following way :-

[
 {"text":"bbb","userID":"U001","time":16704,"showInReport":true},  
 {"text":"bb","userID":"U001","time":167047,"showInReport":true}
]

interval note column containing data

how to find value of key text which contains ‘bb’
which postgersql query can be used to find results

I used below query which works but if someone gives value as userid or text then it shows wrong result

I’m using Postgres 10.20 version

select distinct(workflowid) 
from cyto_records r 
  join cyto_record_results rr on (r.recordid = rr.recordid) 
where rr.interval_note::text LIKE '%aaa%';
Tags:

3

Answers


  1. 0

    Try using JSON_EXTRACT

    SELECT A.* FROM (    
SELECT JSON_EXTRACT(note, '$.text') as val FROM cyto_records) A
WHERE A.val = 'bb'

    ref: https://dev.mysql.com/doc/refman/8.0/en/json.html#json-paths

    Login or Signup to reply.
  2. 0

    For Postgres versions 11 and older, you need to iterate over the array elements:

    select distinct r.workflowid
from cyto_records r 
  join cyto_record_results rr on r.recordid = rr.recordid
where exists (select *
              from json_array_elements(rr.interval_note) as x(item)
              where x.item ->> 'text' like '%bb%')

    If you are using Postgres 12 or newer, you can use a JSON path expression:

    select distinct r.workflowid
from cyto_records r 
  join cyto_record_results rr on r.recordid = rr.recordid
where rr.interval_note @@ '$[*].text like_regex "bb"'

    This assumes that interval_note is defined with the recommended type jsonb. If it’s not, you need to cast it: interval_note::jsonb

    Unrelated to your question, but: the distinct operator is not a function.
    Enclosing a column after that in parentheses is useless won’t change a thing distinct (a),b is the same as distinct a,(b) or distinct a,b

    In fact it’s an "option" to the SELECT keyword: SELECT ALL vs. SELECT DISTINCT – similar to UNION ALL and UNION DISTINCT.

    Login or Signup to reply.
  3. 0

    I would suggest to first flatten using lateral join and then perform ‘plain’ select.
    Postgres version before 12:

    select distinct workflowid
from cyto_records r 
  join cyto_record_results rr on (r.recordid = rr.recordid), 
  lateral (select j->>'text' from json_array_elements(rr.interval_note) j) l(txt)
where txt ~ 'bb';

    Postgres version 12+ using jsonb_path_query:

    select distinct workflowid
from from cyto_records r 
  join cyto_record_results rr on (r.recordid = rr.recordid), 
  lateral jsonb_path_query(rr.interval_note, '$[*].text') txt
where txt::text ~ 'bb';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search