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

How to get a specific key from jsonb in postgresql?

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

Tags:

2

Answers


  1. 0

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

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

Back To Top
Search