title: Joining data from JSON array object field with another table
body: <p>Let say, I have two tables on Postgre database like below:</p>
  <pre><code>CREATE TABLE &quot;MST&quot;.&quot;Users&quot; (
    &quot;Id&quot; uuid NOT NULL DEFAULT uuid_generate_v4(),
    &quot;Uid&quot; varchar(32) COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
    &quot;Pid&quot; varchar(100) COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
    &quot;Details&quot; jsonb,
    &quot;Contacts&quot; jsonb,
    &quot;Trackers&quot; jsonb NOT NULL,
    &quot;Configurations&quot; jsonb,
    PRIMARY KEY (&quot;Id&quot;),
    UNIQUE (&quot;Uid&quot;)
  );
  
  CREATE TABLE &quot;MST&quot;.&quot;Teams&quot; (
    &quot;Id&quot; uuid NOT NULL DEFAULT uuid_generate_v4(),
    &quot;Details&quot; jsonb NOT NULL,
    &quot;Members&quot; jsonb,
    &quot;Trackers&quot; jsonb NOT NULL,
    PRIMARY KEY (&quot;Id&quot;)
  );
  </code></pre>
  <p>which both tables mapped with the following models:</p>
  <pre><code>    [Table(&quot;Users&quot;, Schema = &quot;MST&quot;)]
      public partial class Users
      {
          [Key]
          public Guid Id { get; set; }
          
          [Required]
          [StringLength(32)]
          public string Uid { get; set; }
          
          [Required]
          [StringLength(100)]
          public string Pid { get; set; }
  
          [Column(TypeName = &quot;jsonb&quot;)]
          public UserDetailsModel Details { get; set; }
  
          [Column(TypeName = &quot;jsonb&quot;)]
          public UserContactsModel Contacts { get; set; }
  
          [Required]
          [Column(TypeName = &quot;jsonb&quot;)]
          public TrackersModel Trackers { get; set; }
  
          [Column(TypeName = &quot;jsonb&quot;)]
          public UserConfigurationsModel Configurations { get; set; }
      }
  
      [Table(&quot;Teams&quot;, Schema = &quot;MST&quot;)]
      public partial class Teams
      {
          [Key]
          public Guid Id { get; set; }
  
          [Required]
          [Column(TypeName = &quot;jsonb&quot;)]
          public TeamDetailsModel Details { get; set; }
          
          [Column(TypeName = &quot;jsonb&quot;)]
          public List&lt;TeamMembersModel&gt; Members { get; set; }
  
          [Required]
          [Column(TypeName = &quot;jsonb&quot;)]
          public TrackersModel Trackers { get; set; }
      }
  
      // Childs classes
      public class TeamMembersModel
      {
          [Required(ErrorMessage = @&quot;Member &quot;&quot;User ID&quot;&quot; is required and can not be empty.&quot;)]
          [StringLength(maximumLength: 32, MinimumLength = 3, ErrorMessage = @&quot;Member &quot;&quot;User ID&quot;&quot; length must be between 3 and 32 chars.&quot;)]
          public string Uid { get; set; }
  
          public bool IsLead { get; set; } = false;
  
          public UserDetailsModel Details { get; set; }
      }
  
      public UserContactModel { 
          ...some properties...
      }
  
      public TrackersModel { 
          ...some properties...
      }
  
      public UserDetailsModel { 
          ...some properties...
      }
  </code></pre>
  <p>And below is data samples from <strong>Teams</strong> table:</p>
  <pre><code>INSERT INTO &quot;MST&quot;.&quot;Teams&quot;(&quot;Id&quot;, &quot;Details&quot;, &quot;Members&quot;, &quot;Trackers&quot;) VALUES ('daf0e3d3-78bf-443b-8b0b-6f74d2eabd7f', '{&quot;Name&quot;: &quot;My First Team&quot;, &quot;Files&quot;: [], &quot;Notes&quot;: null, &quot;Photo&quot;: null}', '[{&quot;Uid&quot;: &quot;fadhly.permata&quot;, &quot;IsLead&quot;: true}, {&quot;Uid&quot;: &quot;humaira.permata&quot;, &quot;IsLead&quot;: false}]', '{&quot;Status&quot;: &quot;RG&quot;, &quot;Created&quot;: {&quot;At&quot;: &quot;2020-08-14T12:15:59.4175028+00:00&quot;, &quot;By&quot;: &quot;SYSTEM&quot;}, &quot;Updated&quot;: null}');
  
  INSERT INTO &quot;MST&quot;.&quot;Teams&quot;(&quot;Id&quot;, &quot;Details&quot;, &quot;Members&quot;, &quot;Trackers&quot;) VALUES ('538f1d58-01cb-4120-b89a-ea25bfacaa25', '{&quot;Name&quot;: &quot;My Second Team&quot;, &quot;Files&quot;: [], &quot;Notes&quot;: null, &quot;Photo&quot;: null}', '[{&quot;Uid&quot;: &quot;fadhly.permata&quot;, &quot;IsLead&quot;: true, &quot;Details&quot;: null}]', '{&quot;Status&quot;: &quot;RG&quot;, &quot;Created&quot;: {&quot;At&quot;: &quot;2020-08-19T10:51:44.5128139+00:00&quot;, &quot;By&quot;: &quot;fadhly.permata&quot;}, &quot;Updated&quot;: null}');
  </code></pre>
  <p>As you can see on my DB data, <strong>Members</strong> field is stored with an array of JSON. I am confused about how to join Teams-&gt;Members-&gt;Details with details from the Users table.</p>
  <p>And this is my query:</p>
  <pre><code>from
      team in _manager.DbContext.Teams
  
  where
      team.Trackers.Status != &quot;DE&quot;
  
  orderby
      team.Details.Name
  
  select
      new Models.Teams
      {
          Id = team.Id,
          Details = team.Details,
          Trackers = team.Trackers,
          Members = team.Members.Select(
              x =&gt; new TeamMembersModel { 
                  Uid = x.Uid, 
                  IsLead = x.IsLead, 
                  Details = _manager.DbContext.Users.First(y =&gt; y.Uid == x.Uid).Details 
              }
          )
              .ToList()
      };
  </code></pre>
  <p>But I've no luck with the above query, and got this error message:
  <code>&quot;Object reference not set to an instance of an object.&quot;</code></p>
  <p>Perhaps, I was missing something, but unsure where is it.</p>
  <hr />
  <p><strong>Note:</strong>
  I am using <a href="https://www.npgsql.org/efcore/index.html" rel="nofollow noreferrer">NPGSQL</a> as an additional/extension of EF engine, as I have added on this question tags too.</p>