How to get a specific key from jsonb in postgresql?-CodePudding

I have column in jsonb named "lines" with many object like this :

[
 {
  "a" : "1", 
  "b" : "2", 
  "c" : "3"
 }, 
 {
  "a" : "4", 
  "b" : "5", 
  "c" : "6"
 }
]

This is my query

SELECT *
FROM public.test
WHERE public.test.lines::jsonb ? '[{"c"}]'

In my query i want to get only rows which contain the "c" key in this array But i have nothing after execution

CodePudding user response：

A quick solution:

SELECT
    'c',
    *
FROM
    jsonb_path_query('[{"a": "1", "b": "2", "c": "3"}, {"a": "4", "b": "5", "c": "6"}]', '$[*].c');

 ?column? | jsonb_path_query 
---------- ------------------
 c        | "3"
 c        | "6"

CodePudding user response：

The ? operator only works with strings, not with json objects. If you want to test if any of the array elements contains a key with the value c you can use a JSON path predicate:

SELECT *
FROM test
WHERE lines::jsonb @@ '$[*].c != null'