Displaying JSON Data From Postgres WIth Laravel Blade

Question

I have a postgres database, and it has a table with two columns ('id' which is the primary key, and 'data', which is storing a JSONB dataset that has title, content, etc.).

Currently, I can only show the whole table, or each 'data', but I can't pull individual data from 'data', i.e. a title.

Here is the route, where I've tried pulling in the data as json, as well as json decode:

Route::get('/home', function () {
    $articles = DB::table('articles')->get();
    $results = json_decode($articles, true);
    return view('home', compact('articles', 'results'));
});

The home.blade.php template. I've tried using article and result to display the title. The best I can get is displaying the entire table, but I haven't been able to call a title:

@foreach ($results as $result)            
  <div>{{ $result->title }}</div>
@endforeach 

@foreach ($articles as $article)            
  <div>{{ $article->title }}</div>
@endforeach 

@foreach ($results as $result)            
  <div>{{$result['data']}}</div> //this shows the whole json object, posted below
@endforeach

 @foreach ($results as $result)            
  <div>{{$result['data']['title']}}</div> //I believe this should work, but I get Illegal String Offset 'title' as an error,
 @endforeach

Some of the errors I've gotten:

htmlspecialchars() expects parameter 1 to be string, array given
Array to string conversion
Illegal string offset 'title'
Something about calling something that's not there

Here's what the json object looks like after json_decode (from $result['data'] above):

{"url": "http://omgili.com/ri/.wHSUbtEfZQrTHDoKYSJbjrpQN.N5MJgWJskXd50cUpWKooC_zdZBj5IfjtQ82V5YE9KjMI9MkoEoWsmLqcSDiWUKMSrDShx9H3vPUjRQuW0sylmueXyZg--", "text": "Cable companies are ove....", "uuid": "8c43aa206860570df0a86ff11f619235dea6e2bf", "title": "Cable companies are looking for ways to limit password sharing", "author": "theverge.com", "rating": null, "thread": {"url": "http://omgili.com/ri/.wHSUbtEfZQrTHDoKYSJbjrpQN.N5MJgWJskXd50cUpWKooC_zdZBj5IfjtQ82V5YE9KjMI9MkoEoWsmLqcSDiWUKMSrDShx9H3vPUjRQuW0sylmueXyZg--", "site": "theverge.com", "uuid": "8c43aa206860570df0a86ff11f619235dea6e2bf", "title": "Cable companies are looking for ways to limit password sharing", "social": {"vk": {"shares": 0}, "gplus": {"shares": 0}, "facebook": {"likes": 0, "shares": 0, "comments": 0}, "linkedin": {"shares": 0}, "pinterest": {"shares": 0}, "stumbledupon": {"shares": 0}}, "country": "US", "published": "2017-12-20T18:17:00.000+02:00", "site_full": "www.theverge.com", "site_type": "news", "main_image": "https://cdn.vox-cdn.com/thumbor/wCruRyorIkyClceG2T4Q0BsYk7Y=/0x73:1020x607/fit-in/1200x630/cdn.vox-cdn.com/assets/4562901/theverge1_1020.jpg", "spam_score": 0, "title_full": "Cable companies are looking for ways to limit password sharing - The Verge", "domain_rank": 496, "site_section": "http://www.theverge.com/tech/rss/index.xml", "replies_count": 0, "section_title": "The Verge - Tech Posts", "site_categories": ["media"], "performance_score": 0, "participants_count": 1}, "crawled": "2017-12-20T18:29:59.008+02:00", "entities": {"persons": [{"name": "rutledge", "sentiment": "none"}, {"name": "tom rutledge", "sentiment": "none"}], "locations": [], "organizations": [{"name": "netflix", "sentiment": "none"}, {"name": "bloomberg", "sentiment": "none"}, {"name": "viacom", "sentiment": "none"}, {"name": "ubs", "sentiment": "none"}, {"name": "espn", "sentiment": "none"}]}, "language": "english", "published": "2017-12-20T18:17:00.000+02:00", "highlightText": "", "ord_in_thread": 0, "external_links": [], "highlightTitle": ""}

Thai Duong Tran · Accepted Answer · 2017-12-22 02:58:27Z

3

the issue with your code is when you query data from Postgres, Eloquent return you a Collection object instead of a JSON string. Therefore, the line:

$results = json_decode($articles, true);

never works. As json_decode function only works with a string (UTF8 encoded string specifically).

The error you see is because $article->data is actually not parsed and remain a string.

Array to string conversion and Illegal string offset errors happen when you treat a string as an array.

Basically to parse/decode the JSON data correctly, you have to go through the collection and convert it manually. You can use Eloquent\Collection::map function to correctly map data to an associative array:

$results = $articles->map(function($article){
   return [
      'id' => $article->id,
      'data' => json_decode($article->data, true)  
   ];
})

answered Dec 22, 2017 at 2:58

Thai Duong Tran

2,54214 silver badges15 bronze badges

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

1 Comment

Alteredorange Over a year ago

Thank you for the thorough explanation and answer!!

Collectives™ on Stack Overflow

Displaying JSON Data From Postgres WIth Laravel Blade

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related