2

How can I remove duplicate line based on "groupName" and keep the line with directoryId="1"?

<Group id="123" groupName="ABC" lowerGroupName="abc" active="1" local="1" createdDate="2017-08-21 09:28:30.581" updatedDate="2017-08-21 09:28:30.581" type="GROUP" directoryId="10100"/>
<Group id="456" groupName="ABC" lowerGroupName="abc" active="1" local="0" createdDate="2017-08-21 09:28:30.634" updatedDate="2017-08-21 09:28:30.634" type="GROUP" directoryId="1"/>
3
  • 1
    Update the question to include the information. You might want to add newlines to make it readable. Ideally you write (part of) your input, the desired output and what you have tried so far. May 20 at 14:52
  • with which language or tool? May 20 at 16:05
  • 4
    What is the specific criteria for choosing the one with directoryId = 1? May 20 at 16:08
5

I don't think uniq is necessarily the right tool for this, as it's meant for whitespace-separated or fixed-width files (apparent from its only two "column"-related options being --skip-fields and --skip-chars), while what you have here is XML-like data where neither the column widths are fixed nor are there any trivial single-character separators between columns (the values of groupName etc. could in principle contain whitespace).

I would instead use tools that are meant to deal with XML.

One option that avoids having to write a script yourself would be XPath-based filtering. How XPath can be used to filter for uniqueness can be understood from answers like these - the important syntax elements are the following-sibling:: and preceding-sibling:: axes. Command-line tools for evaluating XPath expressions can be found in the answers to this question. Of the ones I've tried, the most easily-installed was basex (suggested here) so I'll use that in the following.

If I understood your question correctly, you want to reduce lines (XML elements) with the same groupName to just the last such line (or was there another reason for choosing the line with directoryId="1"?). For an XML document like this:

<Groups>
<Group id="123" groupName="ABC" lowerGroupName="abc" active="1" local="1" createdDate="2017-08-21 09:28:30.581" updatedDate="2017-08-21 09:28:30.581" type="GROUP" directoryId="10100"/>
<Group id="456" groupName="ABC" lowerGroupName="abc" active="1" local="0" createdDate="2017-08-21 09:28:30.634" updatedDate="2017-08-21 09:28:30.634" type="GROUP" directoryId="1"/>
<Groups>

where we had to wrap everything in a root element (Groups) to make it well-formed XML, this requirement can be achieved with the following XPath expression:

/Groups/Group[not(@groupName = following-sibling::Group/@groupName)]

/Groups/Group selects elements to return, which are then filtered using the expression in []. @ selects attributes and following-sibling:: matches all subsequent sibling elements of the current one (cf. here).

Running this through basex yields the expected results:

$ basex -i - '/Groups/Group[not(@groupName = following-sibling::Group/@groupName)]'

# [paste this into the terminal:]

<Groups>
<Group id="123" groupName="ABC" lowerGroupName="abc" active="1" local="1" createdDate="2017-08-21 09:28:30.581" updatedDate="2017-08-21 09:28:30.581" type="GROUP" directoryId="10100"/>
<Group id="456" groupName="ABC" lowerGroupName="abc" active="1" local="0" createdDate="2017-08-21 09:28:30.634" updatedDate="2017-08-21 09:28:30.634" type="GROUP" directoryId="1"/>
</Groups>

# [output:]

<Group id="456" groupName="ABC" lowerGroupName="abc" active="1" local="0" createdDate="2017-08-21 09:28:30.634" updatedDate="2017-08-21 09:28:30.634" type="GROUP" directoryId="1"/>

The downside compared to uniq is that basex will first read the entire XML document into memory, so for very large files exceeding the main memory size, this isn't viable. There are XML processors that operate on XML in a streaming manner, e.g. XSLT 3.0 has streaming transformations, so if you have to handle huge files, there is probably a way to do this using any XSLT 3.0 capable processor. But at that point it might be easier to just write your own small streaming parser by hand.

4

Assuming the XML document is well formed, such as

<Groups>
<Group id="123" groupName="ABC" lowerGroupName="abc" active="1" local="1" createdDate="2017-08-21 09:28:30.581" updatedDate="2017-08-21 09:28:30.581" type="GROUP" directoryId="10100"/>
<Group id="456" groupName="ABC" lowerGroupName="abc" active="1" local="0" createdDate="2017-08-21 09:28:30.634" updatedDate="2017-08-21 09:28:30.634" type="GROUP" directoryId="1"/>
</Groups>

(I've just added a root node called Groups), then you may use xq, the XML parser wrapper around jq, from https://kislyuk.github.io/yq/, like so:

xq -x '.[].Group |= unique_by(."@groupName")' file.xml

This keeps only the unique Group nodes by their groupName attribute. The first seen node for a value of the attribute will be kept.

The result of the above command when applied to the XML at the top:

<Groups>
  <Group id="123" groupName="ABC" lowerGroupName="abc" active="1" local="1" createdDate="2017-08-21 09:28:30.581" updatedDate="2017-08-21 09:28:30.581" type="GROUP" directoryId="10100"></Group>
</Groups>

To make sure that you get the one with the lowest directoryID attribute value, sort the nodes by this value first, before uniquifying the list:

xq -x '.[].Group |= (sort_by(."@directoryId") | unique_by(."@groupName"))' file.xml

This would result in

<Groups>
  <Group id="456" groupName="ABC" lowerGroupName="abc" active="1" local="0" createdDate="2017-08-21 09:28:30.634" updatedDate="2017-08-21 09:28:30.634" type="GROUP" directoryId="1"></Group>
</Groups>

For reference, since xq is built on top of jq, the expression is actually applied to a JSON document translated from your XML document. The modified JSON document is then translated back to XML. The JSON document that is modified, given the XML at the top of this answer, looks like this:

{
  "Groups": {
    "Group": [
      {
        "@id": "123",
        "@groupName": "ABC",
        "@lowerGroupName": "abc",
        "@active": "1",
        "@local": "1",
        "@createdDate": "2017-08-21 09:28:30.581",
        "@updatedDate": "2017-08-21 09:28:30.581",
        "@type": "GROUP",
        "@directoryId": "10100"
      },
      {
        "@id": "456",
        "@groupName": "ABC",
        "@lowerGroupName": "abc",
        "@active": "1",
        "@local": "0",
        "@createdDate": "2017-08-21 09:28:30.634",
        "@updatedDate": "2017-08-21 09:28:30.634",
        "@type": "GROUP",
        "@directoryId": "1"
      }
    ]
  }
}
1

Identify the line(s) with the identifier: grep 'groupName="ABC"'

From that you want to deselect the specific line with the exclusion criteria: grep -v 'directoryId="1"'

This will give you the lines to remove. Now we can force duplicate lines and specifically eliminate them:

grep 'groupName="ABC"' input-file | grep -v 'directoryId="1"' > to-remove
cat input-file to-remove | sort | uniq -u > output-file

If you want to clean everything up at the end you can add:

rm to-remove input-file
mv output-file input-file

Warning This will rearrange the contents of you input file. If you simply have a listing of entries without additional structure this solution should suffice.

2
  • 2
    There could theoretically be other nodes than Group nodes with those attributes. Also note that XML files may have newlines between attributes.
    – Kusalananda
    May 20 at 17:31
  • @Kusalananda - Very true, and in general I prefer an XSLT/XPath based solution. However, the original post hinted at something simpler. Without amplifying information, my solution does resolve the issue based on the admittedly limited information provided.
    – sadpanduar
    May 20 at 17:40
1

Another answer ignoring the XML nature of the data, but only valid under the assumptions that a) this will be used as a "one-off" and not in a production workflow, b) the order of attributes is exactly the same for each line and c) there will never be any lines with whitespaces in the attributes before groupName (nor inside groupName's own value):

This answer shows how to use awk to filter out duplicates based on whitespace-separated fields. In your case, that would also be awk '!seen[$3]++', as the groupName part is the third whitespace-separated column. However, if I understood it correctly, you want the last line of each "group of duplicates", not the first (which is what the awk one-liner above would give you). To achieve that, you can simply reverse the order of lines using tac before feeding them into awk, and then reverse them again afterwards to restore the original order:

$ tac | awk '!seen[$3]++' | tac

# [paste this into the terminal:]

<Group id="123" groupName="ABC" lowerGroupName="abc" active="1" local="1" createdDate="2017-08-21 09:28:30.581" updatedDate="2017-08-21 09:28:30.581" type="GROUP" directoryId="10100"/>
<Group id="456" groupName="ABC" lowerGroupName="abc" active="1" local="0" createdDate="2017-08-21 09:28:30.634" updatedDate="2017-08-21 09:28:30.634" type="GROUP" directoryId="1"/>

# [output is:]

<Group id="456" groupName="ABC" lowerGroupName="abc" active="1" local="0" createdDate="2017-08-21 09:28:30.634" updatedDate="2017-08-21 09:28:30.634" type="GROUP" directoryId="1"/>

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.