Query on jsonb array

Question

I have this kind of json array and i want to check stringValue inside value array is null or not plus i want to check it with its id and fielddata is column name

[
  {
    "name": "50a5613e97e04cb5b8d32afa8a9975d1",
    "value": {
      "stringValue": null
    }
  },
  {
    "name": "bb127e8284c84692aa217539c4312394",
    "value": {
      "dateValue": 1549065600
    }
  }
]

query is:

select * 
from field 
WHERE (fielddata->>'name') = '50a5613e97e04cb5b8d32afa8a9975d1' 
   AND fielddata->'value'->>'stringValue' IS NOT NULL;

and I want use this query in laravel5.7

Farhan Ali · Accepted Answer · 2019-02-08 06:44:41Z

1

Try this

$result = \DB::table('field')->where('name', "50a5613e97e04cb5b8d32afa8a9975d1" )->where('value', $stringValue)->get();

if(isset($result)){
    foreach($result as $res){
        if(isset($res['value']->stringValue)){
            // not null case
        }else{
            // null case
        }
    }
}

edited Feb 8, 2019 at 6:44

answered Feb 8, 2019 at 6:42

Farhan Ali

2303 silver badges14 bronze badges

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

Comments

score 1 · Accepted Answer · 2019-02-08 06:57:18Z

1

Within a SQL query, I think you want something like this:

select t.*
from the_table t
where exists (select *
              from jsonb_array_elements(t.fielddata) as j(e)
              where e ->> 'name' = '50a5613e97e04cb5b8d32afa8a9975d1' 
                and e -> 'value' ->> 'stringValue' is not null);

The exists sub-query will check every array element and see if at least one element has the specified name and a non-null stringValue. The query will then return the complete row from the table that fulfills the condition.

Online example: https://rextester.com/AGGJNR88809

edited Feb 8, 2019 at 6:57

answered Feb 8, 2019 at 6:43

user330315

Collectives™ on Stack Overflow

Query on jsonb array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related