16

Let's say I have a JSON like the following:

{
    "key1": {
        "keyA": "1",
        "keyB": "null",
        "keyC": "null"
    },
    "key2": {
        "keyA": "null",
        "keyB": "3",
        "keyC": "null"
    }
}

I'd like to find a way of excluding all keys with the value null on my JSON. So the result would be the following:

{
    "key1": {
        "keyA": "1"
    },
    "key2": {
        "keyB": "3"
    }
}

I know I can exclude specific keys with their names using jq, e.g. cat myjson.json | jq 'del(.[]|.keyA)', this will erase all my keyA keys inside the json, but I want to exclude the keys according to their values... How can I exclude all keys with value "null" using jq?

raylight
  • 461
  • 1
  • 6
  • 15
  • Isn't this a bug in the system generating the JSON in the first place? null values should have been omitted. – Thorbjørn Ravn Andersen Jun 18 '21 at 15:53
  • @ThorbjørnRavnAndersen I'm not sure if it's a bug... I have a spatial database with SpatiaLite and this database has many null values. When I export a part of this database with ogr2ogr as a GeoJSON file it brings me all null values inside the database in this GeoJSON file. So this is a workaround to exclude all irrelevant data and make my JSON as small as possible. – raylight Jun 18 '21 at 16:37
  • I'm wondering why it contains the string "null" instead of the actual JSON value null. – chepner Jun 18 '21 at 17:17
  • @chepner Actually it contains the value null... Writing the string "null" was one typo that I made on the question. – raylight Jun 18 '21 at 18:44

2 Answers2

25
del(..|select(. == "null"))

This uses the recursive-descent operator .. and select function to find all the locations anywhere in the object with values that are equal to "null" and gives them to del. select evaluates a boolean expression to decide whether to include a particular value or not, while .. gives every single value in the tree to it, and del accepts any expression that produces locations, which this does. (demo)

You can use the path function to check what it's finding:

path(..|select(. == "null"))

and debug what it thinks you're trying to delete first. The output is an array of keys to follow recursively to reach the value, and so the final item in the array is the key that would actually be deleted.

You can also use update-assignment with |= empty in jq 1.6 and up (it silently fails in earlier versions). You may or may not find this clearer: (..|select(. == "null")) |= empty (demo) deletes those same keys.

If your values are true nulls, rather than the string "null", you can use the nulls builtin function in place of the whole select: del(..|nulls).

If your values are true nulls and you're using jq 1.5 (the current version in many distributions), you'll need to use recurse(.[]?; true) in place of ... This is because null values are specifically excluded from .. (because it's defined as recurse, which is defined as recurse(.[]?), which is defined as recurse(.[]?; . != null)). This behaviour changed to the (more useful) one above in 1.6, though the documentation hasn't changed, which appears to be a documentation bug.

Michael Homer
  • 76,565
  • Thanks... I'm not sure if I caught an edge case but when I use del(..|select(. == null)) on the json from my application it seems that the recursivity is not working. I'm struggling here trying to figure out why the select is not working on this file. Weird thing is that when I create a simple json file with an array of objects inside my objects it works fine. Would you know to explain why the recursivity doesn't work with this json file? ( in this case it's null instead of "null") – raylight Jun 18 '21 at 06:59
  • 2
    @raylight If you're running jq 1.5, .. excludes true null values from consideration. You can replace .. with recurse(.[]?;true) and it should work - .. works in 1.6 (though the documentation actually still says it shouldn't. You can also simplify select(.==null) to just the nulls builtin. – Michael Homer Jun 18 '21 at 07:23
  • 1
    Thanks again! You're right, I'm using jq 1.5 and cat file.json | jq 'del(recurse(.[]?;true)|select(. == null))' worked for me... I realized that when I put my json on your demo page the command with del(..|select(. == null)) works fine, so the difference here is the version. – raylight Jun 18 '21 at 07:32
  • 1
    Great! I actually did not know this had changed, and always thought it had worked the new way. The explicit recurse(.[]?;true) will continue to work after any future upgrade to 1.6 anyway, so you won't need to update anything. And to be fair, the demo page is an independent in-browser reimplementation, so it's not definitive (just convenient for live editing), but it does match the current version's behaviour here. – Michael Homer Jun 18 '21 at 07:37
  • @MichaelHomer they fixed the docs – TWiStErRob Jul 24 '23 at 11:28
4

Assuming that you know you want to test the values at the 2nd level:

jq 'del(.[][] | select(. == "null"))' file

This deletes every key-value pair on the 2nd level from the top, where the value is the literal string null.

Given the document in the question, this produces the expected output.

Using a version of jq newer than 1.5, you could also just update the data to the bits that don't have the null value:

jq '.[][] |= select(. != "null")' file
Kusalananda
  • 333,661
  • Note that the last approach is also jq 1.6-only because it relies on the new "empty RHS means delete" update-assignment behaviour. It will fill your object with nulls on earlier versions. The first version works everywhere. – Michael Homer Jun 18 '21 at 09:41
  • @MichaelHomer Thanks. I don't have an older version to test with at the moment, but I'll insert a caveat in the text about this. – Kusalananda Jun 18 '21 at 09:43
  • The jq 1.5 result is { "key1": { "keyC": null }, "key2": { "keyA": null, "keyB": null, "keyC": null } } (I actually don't understand where the keys have gone in the first subobject there). – Michael Homer Jun 18 '21 at 09:45
  • @MichaelHomer Huh. And I wonder why keyC was the one not deleted? Anyway... – Kusalananda Jun 18 '21 at 10:02