Only the active users, as CSV

Someone in finance wants a spreadsheet of the accounts that are still active, and what you have is the members export: every account, active or not, inside a wrapper object. Converting the whole file would hand them rows they then have to filter by hand, and the wrapper would not become a table at all. The fix is to say which part you want before converting. Convert has a Path field for exactly that, and the editor is the place to write the path, because there you can see what it matches before a single row is written.

1 · Write the filter where you can see it

Paste the export into the editor and try the expression in the Find bar first. A filter that is slightly wrong — a typo in the key, a string where the data has a boolean — matches nothing, and it is far easier to see that here, with each match marked in the text and the tree, than in an empty result on another page.

{
  "users": [
    { "id": 1, "name": "Noor", "email": "[email protected]", "active": true },
    { "id": 2, "name": "Tomasz", "email": "[email protected]", "active": false },
    { "id": 3, "name": "Aiko", "email": "[email protected]", "active": true },
    { "id": 4, "name": "Femi", "email": "[email protected]", "active": false }
  ]
}

1/2
/users/0
/users/2

Two matches, the first and the third user, and the pointers under the counter say which. Step through them with Enter to see each one marked. Note the comparison: == true, with no quotes, because the data holds a boolean. Writing == 'true' would compare against a string and match nobody, which is a mistake worth making here rather than in a spreadsheet that arrives empty.

The expression reads left to right: start at the root, go into users, and keep each element for which the filter in [?…] holds, where @ stands for the element being tested. That is all this recipe needs; the JSONPath guide has the rest.

2 · Give Convert the same path

The search did not change the document, so it goes to Convert exactly as it was. Paste it, or press Try it below, which also types the path in for you.

{
  "users": [
    { "id": 1, "name": "Noor", "email": "[email protected]", "active": true },
    { "id": 2, "name": "Tomasz", "email": "[email protected]", "active": false },
    { "id": 3, "name": "Aiko", "email": "[email protected]", "active": true },
    { "id": 4, "name": "Femi", "email": "[email protected]", "active": false }
  ]
}

id,name,email,active
1,Noor,[email protected],true
3,Aiko,[email protected],true

Path $.users[[email protected] == true]: 2 matches, as an array.

Two rows, one per active user, with a header row taken from the keys. The active column is kept, and says true in both rows — redundant now, and simple to delete once the file is open, or to leave as a record of what was asked for.

The line under the table is Convert explaining the shape. Because the path has a filter, it could have matched any number of users, so Convert collected the matches into an array — and an array of objects is exactly what a table needs. A path with no filter or wildcard, such as $.users, would have given you the one array it points at instead, which here is every user.

Before you download

Row labels is the option most likely to surprise. When it is on, the first column holds each row’s position in the array, which is useful when the table and the JSON are going to be compared later and noise when they are not; the steps untick it for that reason. The choice is remembered, along with the header row, so check both the next time you convert.

The download’s file name follows the path: the node the path picks is added to the document’s name, so the result is named after the users rather than after the whole export. The file itself starts with a byte-order mark and ends each line with CRLF, which is what Excel needs to read accented names correctly; neither shows in the result pane or in the block above.

Other questions of the same export

Anything the Find bar can match, Convert can write. Change the filter to $.users[[email protected] == false] for the lapsed accounts, or select one column with $.users[*].email for a plain list of addresses — though that gives a list of strings, which Convert writes as a one-column table. For TSV instead of CSV, change To; for a table to paste into an issue, choose a Markdown table. The path stays as it is.

If the export is YAML rather than JSON, nothing changes but From: the path is applied to the JSON Convert reads the YAML into, and the JSON tab shows you that reading in full, so you can write the path against it.

Where to go from here

• Query JSON with JSONPath and its cheat sheet.
• Convert one part of a document: one value or an array of matches, and the refusals.
• CSV and TSV: the delimiter and the header row.

Open the editor to try a filter on your own export.