0

I stuck on how to get two specific columns of data. My query:

{site}/_api/web/lists('{site_guid}')/items?$select&fields eq meatballColor,meatballExplained

I only get 7 items coming back when it should be 14, wondering if my syntax is correct.

enter image description here

1 Answer 1

0

TL;DR

The correct syntax for a $select that returns only column values from two columns (using your example for column names) is:

{site}/_api/web/lists('{list_guid}')/items?$select=meatballColor,meatballExplained

but that is still going to return all the items in the list, which according to your screen shot looks like 7.


First, from the screenshot of the list that you provided, it looks like there are only 7 items in the list. So when you say

I only get 7 items coming back when it should be 14

If you are thinking that you should be getting 14 results because there are 7 list items but you are asking for 2 column values, so you are expecting 7 x 2 = 14 things, that's not how the results are going to come back.

You can use a $select in your oData query to limit the number of columns returned, but they are still going to be divided into separate objects that correlate to the list items that hold those values in those columns. If you want to limit the number of overall items that get returned, you would need to use a $filter in order to only return the list items that match your specific criteria.

For instance, if you did a query on that list with no $select to restrict the columns:

{site}/_api/web/lists('{list_guid}')/items

you would get a result that looked something like this (although with a lot more fields/columns included, because there are a bunch of built-in hidden SharePoint fields):

{
    d: {
        results: [
            {                // <-- first list item object
                Title: "y",
                default: "up",
                meatballExplained: "Choice #1",
                meatballColor: "Green",
                // other built-in SP fields
            },
            {                // <-- second list item object
                Title: "u",
                default: "down",
                meatballExplained: "Choice #3",
                meatballColor: "Red",
                // other built-in SP fields
            },
            // and then the other 5 list item objects here
        ]
    }
}

Now, if you used a $select to limit the fields, you're still going to get a results array of 7 items, because you are not using a $filter to limit the number of items, however, you would see less data inside each list item object.

Your $select query is also not quite the right syntax. Instead of

/items?$select&fields eq meatballColor,meatballExplained

what you want is

/items?$select=columnOneName,columnTwoName

so in your specific case

{site}/_api/web/lists('{list_guid}')/items?$select=meatballColor,meatballExplained

And the result you get from that will look something like

{
    d: {
        results: [
            {                // <-- first list item object
                meatballExplained: "Choice #1",
                meatballColor: "Green"
            },
            {                // <-- second list item object
                meatballExplained: "Choice #3",
                meatballColor: "Red"
            },
            // and then 5 more list item objects here
        ]
    }
}

Notice that Title and default are no longer included (since they are not part of the $select), but you still are going to get all 7 list items.

Now, if you used a $filter to limit the number of items based on certain criteria, you could do something like

{site}/_api/web/lists('{list_guid}')/items?$select=meatballColor,meatballExplained&$filter=meatballColor eq 'Yellow'

which is telling SharePoint "give me all the list items where meatballColor is Yellow (but not any others), but only give me the values of meatballColor and meatballExplained for those few list items."

What you would get would look something like

{
    d: {
        results: [    // <-- the 'results' array will only contain 2 items, not all 7
            {
                meatballExplained: "Choice #3",
                meatballColor: "Yellow"
            },
            {
                meatballExplained: "Choice #5",
                meatballColor: "Yellow"
            }
        ]
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.