0

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%';
Improve this question

3 Answers 3

Reset to default
1

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

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

5 Comments

I don't want exact value but I want value which contains bb means can be any value like bbb, bbbb, abbc etc.
ERROR: function json_extract(json, unknown) does not exist LINE 2: SELECT JSON_EXTRACT(interval_note, '$.text') as val FROM cyt... HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 30
what is your mysql version? as verion 5.7+ supports json functions
also, as you did in your query you can use like instaead of equal comparision operator in A.val = will be A.LIKE
Postgres has no json_extract function. But even in MySQL this wouldn't work, as the top level of the JSON value is an array and JSON_EXTRACT(note, '$.text') won't iterate over the array and will simply return null
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.

Improve this answer

8 Comments

I'm using Postgresql I'm getting error as "ERROR: operator does not exist: json @? unknown LINE 7: and rr.interval_note @? '$[*].text like_rege... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. SQL state: 42883 Character: 193"
Then you are using something older than Postgres 12. See my edit
Is this a unique question in 2022? Ideally, we are trying to get all answers on a given topic in one place.
@mickmackusa: I am pretty sure this is a duplicate. But SO's search capabilities make it really hard to find such a question. It's actually faster for me to write the answer than to search for a duplicate.
I'm using Postgres 10.20 version
|
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';
Improve this answer

4 Comments

ERROR: function jsonb_path_query(json, unknown) does not exist LINE 2: from cyto_record_results t, lateral jsonb_path_query(interva... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 67
ERROR: function jsonb_array_elements(json) does not exist LINE 2: ...cord_results t, lateral (select j ->> 'text' from jsonb_arra... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 93
Your Postgres version is older than 12 so pls. use the first query.
Then replace jsonb_array_elements with json_array_elements. Answer updated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.