👋 Next.js Conf 2024: Come build, party, run, and connect with us! See all events

Discussion on querying nested JSON data in GROQ and structuring data in Sanity for easier querying.

25 replies
Last updated: Aug 19, 2020
Hey team, no way to perform GROQ queries on a field that contains string of json within a doc right? So if we had a field
data
with a value
"[ { "id":"one" }, { "id":"two"}, ... ]
, we wouldn’t be to query that nested json data?
Aug 18, 2020, 5:18 PM
If you’re looking to run one GROQ query to grab a piece of data from that string, I believe you won’t be able to, since GROQ will just view that as a string and not as JSON. You could, in theory return the JSON string and then use GROQ in your application code to grab a field, but if the data is simple enough, that would probably be overkill.
How are you getting the JSON string into your data?
Aug 18, 2020, 6:11 PM
If you’re looking to run one GROQ query to grab a piece of data from that string, I believe you won’t be able to, since GROQ will just view that as a string and not as JSON. You could, in theory return the JSON string and then use GROQ in your application code to grab a field, but if the data is simple enough, that would probably be overkill.
How are you getting the JSON string into your data?
Aug 18, 2020, 6:11 PM
Thanks
user T
. So yea the second solution is what I’m trying to avoid. Basically I have a doc with
_id = apiData
in my schema that collates all of my site’s 3rd party data into a json string within a field for that doc labeled
data
. The goal here is to push all 3rd party data to sanity so I can run groq queries against everything.
Aug 18, 2020, 7:45 PM
For a couple of reasons it would be MUCH more convenient if the third party data and my sanity data were all part of the same dataset when running groq queries as they are tied into one another.
Aug 18, 2020, 7:46 PM
It might take a little more effort on the front, but instead of pushing a JSON string into a string field, you could structure that data into an object and push that object to a corresponding object (or array, i suppose) schema. Depending on what 3rd party data you’re working with you could do some really interesting things with it if structured
Aug 18, 2020, 7:56 PM
if you’ve got a small snippet of the JSON that you’d be comfortable sharing, i’d be curious to see what the overall shape of things is. I feel like housing third-party data is a really interesting use case
Aug 18, 2020, 7:56 PM
Thanks, yea I had discussed this in some DMs with
user A
. I was thinking of maybe setting things up within the schema, I guess a custom object/array is a good idea. The lazy me was hoping to push the data in there regardless of the shape.
Aug 18, 2020, 8:15 PM
Basically I’m trying to replicate how Gatsby collates everything into a single GraphQL schema, except I want it to do it with NextJS+ Sanity+GROQ
Aug 18, 2020, 8:18 PM
I have some other data, but one major piece is an array of objects with the following structure:

{
  Id: 'a004o000006S8QNAA0',
  Name: 'Part 107 Test Prep - In Person - Austin, TX 11/8/2020',
  Date_Offered__c: '2020-11-08',
  SKU__c: 'OP201107AUTX08NOVSundayGround2',
  Location_City__c: 'Austin',
  Location_State__c: 'TX',
  Product2__r: {
    Id: '01t46000001BbYzAAK',
    Name: 'Part 107 Test Prep - In Person',
    shortID: '01t46000001BbYz'
  },
  Facility__r: {
    Name: 'Hampton Inn and Suites - Austin South/Buda',
    Class_City__c: 'TX - Austin',
    BillingStreet: '1201 Cabelas Drive',
    BillingCity: 'Buda',
    BillingPostalCode: '78610',
    BillingState: 'TX'
  },
  Contact__c: 'xxx',
  Class_Status__c: 'Upcoming',
  Sold_Out__c: false,
  Seats_Available__c: 8,
  shortID: 'a004o000006S8QN',
  slug: 'tx-austin',
  instructor: {
    _createdAt: '2019-10-24T13:23:12Z',
    _id: 'o3sJ5YAauiy3u5Qul2kUcT',
    _rev: 'EPjlJ1Z1osBysrKoTHrQFl',
    _type: 'instructor',
    _updatedAt: '2020-06-11T13:35:42Z',
    bio: [Array],
    email: 'xxx',
    experience: '10 years',
    expertise: 'Aerial photography and videography, Video production, Broadcast media ',
    fullName: 'Jerry White',
    instructorHours: '696',
    name: 'Jerry White',
    photo: [Object],
    pilotRating: 'Airline Transit Pilot, Multi-engine, Small Unmanned Aerial Systems',
    sfID: '0034600000gm8kFAAQ',
    slug: [Object],
    state: [Object],
    title: 'Airline Pilot & Business Owner'
  }
},
Aug 18, 2020, 8:26 PM
The
instructor
property actually contains data from Sanity
Aug 18, 2020, 8:26 PM
You can push this to a document. if your schema isn’t set up, you’ll get a warning in Studio, but the data will exist on your document and can be queried (just fyi)
Aug 18, 2020, 8:29 PM
I can definitely understand not wanting to manually set up the schema for that json 😄
Aug 18, 2020, 8:29 PM
I can definitely understand not wanting to manually set up the schema for that json 😄
Aug 18, 2020, 8:29 PM
oooooh good call I forgot that documents don’t necessarily need predefined schemas in order to work on the backend
Aug 18, 2020, 8:30 PM
yup, as long as you don’t mind that warning, which i’d think this would be a singleton document that you’d not edit in Studio anyway…
Aug 18, 2020, 8:31 PM
hmm thanks ALOT I’m going to play around with it to see if it’s too good to be true
Aug 18, 2020, 8:32 PM
Glad to help!
Aug 18, 2020, 8:34 PM
YEEESSSS! I update the data and am successfully able to run this query:

*[ _id == "apiData" ] {
  "classes": @.sfClasses[ Location_State__c == "NY" ] {
    ...
  }
}
Aug 18, 2020, 8:41 PM
All your data in one place 🎉 😄
Aug 18, 2020, 8:41 PM
Thanks again
user T
Aug 18, 2020, 8:41 PM
Yes
Aug 18, 2020, 8:41 PM
And easy to update via webhook
Aug 18, 2020, 8:42 PM
Hmm revisiting this. One catch here is that while the structure is fine if we just need to query each third-party data in isolation, it becomes tricky when trying to include them in subqueries. For example, in my Sanity dataset I have some
inPersonClass
docs, each of which have a Salesforce product ID
sfID
. Now in my
apiData
doc holding all third-party data, I have a
sfClasses
array holding all of my Salesforce classes. I am first trying to query some
inPersonClass
, grab their
sfID
, then run a query on that
sfClasses
array within my
apiData
. Here is what I am trying, but getting stuck because we can’t reference grandparent scope:

{
  "course": *[ _type == "inPersonClass" && slug.current == $slug ][0] {
    _id,
    sfID,
    "api": *[ _id == "apiData" ][0] {
      "classes": sfClasses[ Product2__r.Id match ^.sfID + "*" ] {
        ...
      }
    }
  }
}
I might be missing something here so am reaching out. Would be awesome if we could set variables within projections and then use those variables further down in the query.
Aug 19, 2020, 12:47 PM
Ah I think I got it. Looks to be doable by filtering the original projection:

{
  "course": *[ _type == "inPersonClass" && slug.current == $slug ][0] {
    _id,
    sfID,
	"apiData": *[ _id == "apiData" ][0] { 
      sfClasses
    }
  } | [] {
    ...,
    "apiData": {
    	"sfClasses": ^.apiData.sfClasses[ Product2__r.Id match ^.sfID + "*" ] {
        ...
      }
  	}
  }
}
Aug 19, 2020, 1:21 PM
That makes sense to me! Also, thanks for responding to seb who was also having this issue. This was the suggestion I was going to give them 😄
Aug 19, 2020, 1:42 PM

Sanity– build remarkable experiences at scale

Sanity is a modern headless CMS that treats content as data to power your digital business. Free to get started, and pay-as-you-go on all plans.

Was this answer helpful?