0

I need to develop a graphql server to query data directly from a JSON object. this JSON object is stored in a postgres database table as below.

enter image description here

This value field can be any JSON object. I don't have control over that. it's directly coming from a SharePoint server. I need to query this JSON object dynamically using graphql.

What I want here is to query JSON object and get only what I need instead of getting all the JSON data. for example like below

query {
  allBookings {
    id,
    listId
    widget {
      text  {
        name
        style
        onMouseUp
      }
    }
  }
}

currently, my result is this. enter image description here

Technologies I am using

  • .NET 6
  • Postgresql
  • HotChocolate

this is my code so far.

[Table("bookings")]
public class Booking
{
    [Column(name: "id")]
    public int Id { get; set; }
    [Column(name: "list_id")]
    public Guid ListId { get; set; }
    [Column(name: "value", TypeName = "jsonb")]
    [GraphQLType(typeof(AnyType))]
    public string Value { get; set; }
}

public class BookingType : ObjectType<Booking>
{
    private readonly IDbContextFactory<DemoDBContext> _factory;

    public BookingType(IDbContextFactory<DemoDBContext> factory)
    {
        _factory = factory;
    }

    [Obsolete]
    protected override void Configure(IObjectTypeDescriptor<Booking> descriptor)
    {
        descriptor.Field(t => t.Id)
            .Type<NonNullType<IntType>>();
        descriptor.Field(t => t.ListId)
            .Type<NonNullType<UuidType>>();
        descriptor.Field(t => t.Value)
            .Type<AnyType>()
            .Resolver(context =>
            {
                var db = _factory.CreateDbContext();
                var value = context.Parent<Booking>().Value;
                return value;
            });
    }
}

public class Query
{
    private readonly IDbContextFactory<DemoDBContext> _factory;

    public Query(IDbContextFactory<DemoDBContext> factory)
    {

       _factory = factory;
    }

    public async Task<IEnumerable<Booking>> GetAllBookings()
    {
        using var context = await _factory.CreateDbContextAsync();

        var bookings = await context.Bookings.ToListAsync();
        return bookings;
    }

    public async Task<Booking> GetBooking(Guid id)
    {
        using var context = await _factory.CreateDbContextAsync();

        var booking = await context.Bookings.Where(x => x.ListId == id).FirstOrDefaultAsync();

        return booking;
    }
}

I've tried different methods but I don't have a clear idea to implement this kind of behavior or even is this possible to do.

if there's a better way of doing this please suggest me. Thanks all.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Buddhika Nelum
  • 33
  • 1
  • 12
  • `This value field can be any JSON object. I don't have control over that. it's directly coming from a SharePoint server` you should rethink this. SharePoint content types *do* have schemas. SharePoint doesn't return arbitrary JSON, it returns items of a specific content type serialized as JSON or XML. Whether you use OData and SharePoint or GraphQL over some database, the schema is known in advance – Panagiotis Kanavos Jan 10 '23 at 16:17
  • Why not call *SharePoint* directly? OData is equivalent to GraphQL and can be used with (relatively) simple HTTP GET calls. SharePoint's .NET client libraries make this easier. Why cache the serialized data in PostgreSQL? Are you trying to create a GraphQL adapter for SharePoint? Caching the serialized list items in PostgreSQL doesn't help and introduces concurrency issues - what happens if the list items are edited? – Panagiotis Kanavos Jan 10 '23 at 16:23
  • @PanagiotisKanavos Thanks for the reply. Yes. my client needs to create a wrapper around SharePoint for some specific reasons. if the list items changed from the SharePoint side it reflects in the Postgres table in real-time using a syncing service. – Buddhika Nelum Jan 10 '23 at 16:59
  • No it doesn't. There's no such service. You may have *a custom service* that periodically exports list items to PostgreSQL, but that's not real time. You added a level of indirection that doesn't help with *this* problem. At best, it's a cache of the SharePoint data. You *do* need the content type schema, both in SharePoint and GraphQL. Both are schema bound – Panagiotis Kanavos Jan 10 '23 at 17:01
  • @PanagiotisKanavos There's a custom service that is doing the synching process. If any change happened to the SharePoint side it's synching to the Postgres database through rabbitMQ within seconds. – Buddhika Nelum Jan 10 '23 at 19:04

1 Answers1

0

GraphQL will automatically filter out fields that are either not requested or not part of a model.

If you define your types as:

type Booking {
  id: ID!
  listID: String
  value: Widget
}
type Widget {
  text: SubWidget
}
type SubWidget {
  name: String
  style: String
  onMouseUp: String
}
query allBookings: [Booking]

In your resolver you're going to return an array of JSON objects corresponding to each Booking. If that JSON has fields that are not part of your type definitions they will not be returned. If some of the fields you ask for are missing then they will come back as undefined unless you make them non-nullable (ex: name: String!)

So you're most of the way there.

Michel Floyd
  • 18,793
  • 4
  • 24
  • 39
  • Thanks for the reply. The thing is I cannot define types because there might be another JSON that is completely different from this. That's where I'm stuck. – Buddhika Nelum Jan 10 '23 at 19:09
  • If you have a small number of possible types then you can define a [union type](https://www.apollographql.com/docs/apollo-server/schema/unions-interfaces/) that lets you return any one of them. – Michel Floyd Jan 10 '23 at 19:32