Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: mongodb output - update array element

  1. #1
    Join Date
    Nov 2013
    Posts
    4

    Default mongodb output - update array element

    Hi,

    i need to update elements in an array, but could not find a solution. please find details below


    this is my table input:

    select '123' partner_customer_id, '222' order_id, '1' quantity union all
    select '123' parner_customer_id, '333' order_id, '2' quantity

    and this is my mongo output:


    Name Mongo document path Use field name Match field for upsert Modifier operation Modifier policy
    partner_customer_id Y Y N/A Insert
    partner_customer_id Y N N/A Insert
    order_id sales[0] Y N $set Insert
    order_id sales[1] Y N $push Update
    quantity sales[0] Y N $set Insert
    quantity sales[1] Y N $push Update

    please find below the screenshot about the mongodb output
    http://cl.ly/image/18442w163v1x


    When running the above it inserts a customer record with an two element array. (the two orders ) if running it again there will be a customer record with an 4 element array, etc.

    But i want to reach that it updates the array elements as well based on the order_id field. eg the quantity changes from 1 to 4 then it updtes the quantity, but array element remains the same only change: quantity is updted in the relevant element. It is possible to do this in pentaho?

    thanks in advance
    Last edited by ztalas; 11-06-2013 at 06:40 AM.

  2. #2
    Join Date
    Jun 2015
    Posts
    3

    Default mongodb output - update array element

    Hi ztalas,could I ask whether you solve this problem? How to remove duplicate fields when executing update? many thanks for advice.

  3. #3

    Unhappy The field must be an array but is of type Object in document {INVALID-MUTABELEMENT}

    Jupiter -

    Did you find an answer to this? I have tried this at least 7 different ways just using a simple data grid to test (see attached)
    JSON ArrayTesting.ktr

    None of them work well with a modifier update upsert.

    When I try and select an attribute of the object in the array as a key, and apply Match Field for Update, I get this error. You can see that by connecting Test 7 and launching the transformation.

    2015/08/18 10:33:42 - Upsert and Modifier - Push with I/U on all - Match ID field for Update | FAIL THROWS ERROR.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : An error occurred during write: com.mongodb.WriteConcernException: { "serverUsed" : "<DB NAME>:<PORT>" , "ok" : 1 , "n" : 0 , "updatedExisting" : false , "err" : "The field 'Arrayer' must be an array but is of type Object in document {INVALID-MUTABLE-ELEMENT}" , "code" : 16836}
    ...
    2015/08/18 10:34:32 - Upsert and Modifier - Push with I/U on all - Match ID field for Update | FAIL THROWS ERROR.0 - at java.lang.Thread.run(Unknown Source)
    2015/08/18 10:34:32 - Upsert and Modifier - Push with I/U on all - Match ID field for Update | FAIL THROWS ERROR.0 - Caused by: com.mongodb.WriteConcernException: { "serverUsed" : "<DB NAME>:<PORT>" , "ok" : 1 , "n" : 0 , "updatedExisting" : false , "err" : "The field 'Arrayer' must be an array but is of type Object in document {INVALID-MUTABLE-ELEMENT}" , "code" : 16836}




    Quote Originally Posted by Jupiter View Post
    Hi ztalas,could I ask whether you solve this problem? How to remove duplicate fields when executing update? many thanks for advice.

  4. #4
    Join Date
    Apr 2017
    Posts
    5

    Default

    Hello,

    Anyone got the solution for this problem?

    Thanks in advance.

  5. #5
    Join Date
    Feb 2017
    Posts
    14

    Default

    Since the mongo java driver sends over json I would try to figure how this json insert should look like. If it is not possible to do it using the standard way on the db output step then you could always create a json string and insert it directly into mongo from the db output step.

    I have done it like this using a user defined java class and bson serializers which creates a json string which I then insert directly into mongo using the mongo db output step. Which coincidently is 4x faster as there are no silly reflection calls or path lookups.

    Quote Originally Posted by ervison View Post
    Hello,

    Anyone got the solution for this problem?

    Thanks in advance.

  6. #6
    Join Date
    Apr 2017
    Posts
    5

    Default

    Quote Originally Posted by tkaszuba View Post
    Since the mongo java driver sends over json I would try to figure how this json insert should look like. If it is not possible to do it using the standard way on the db output step then you could always create a json string and insert it directly into mongo from the db output step.

    I have done it like this using a user defined java class and bson serializers which creates a json string which I then insert directly into mongo using the mongo db output step. Which coincidently is 4x faster as there are no silly reflection calls or path lookups.

    Thanks tkaszuba, for your reply. Unfortunaly, I am a newbie in Pentaho Keetle. My transformation have a Sql Table as input and Mongodb as output

    On mongodb, the document is like that:

    {
    "_id" : ObjectId("58e2c775781a75592f69f75b"),
    "DDATA_ORC" : ISODate("2016-08-02T03:00:00.000Z"),
    "SNUMORC" : "113244",
    "PROD" : [
    {
    "SPRODUTO" : "PAPEL TOALHA BCO. NOBRE C/1000 - 100% CELULOSE"
    },
    {
    "SPRODUTO" : "TONER HP Q7553A BLACK (53A)"
    },
    {
    "SPRODUTO" : "TONER HP Q7553X BLACK (53X)"
    }
    ]
    }

    If I run the job again, The Prod array receives all items again and It will be multiplicated

    DDATA_ORC" : ISODate("2016-08-02T03:00:00.000Z"),
    "SNUMORC" : "113244",
    "PROD" : [
    {
    "SPRODUTO" : "PAPEL TOALHA BCO. NOBRE C/1000 - 100% CELULOSE"
    },
    {
    "SPRODUTO" : "TONER HP Q7553A BLACK (53A)"
    },
    {
    "SPRODUTO" : "TONER HP Q7553X BLACK (53X)"
    },
    {
    "SPRODUTO" : "PAPEL TOALHA BCO. NOBRE C/1000 - 100% CELULOSE"
    },
    {
    "SPRODUTO" : "TONER HP Q7553A BLACK (53A)"
    },
    {
    "SPRODUTO" : "TONER HP Q7553X BLACK (53X)"
    }
    ]

    My Mongodb Ouput Document Fields:
    Name:  mongdboutpup_schema.jpg
Views: 459
Size:  12.3 KB

    The MongoDb Options:
    Name:  mongodb_output_options.jpg
Views: 465
Size:  15.1 KB

    Could you post an example that shows your solution for this problem?

    Any help will be appreciated.

  7. #7
    Join Date
    Apr 2017
    Posts
    5

    Default

    Thank for you reply.

    Do you have some example? I am newbie in Pentaho Keetle.

    My Document Path:
    Name:  mongdboutpup_schema.jpg
Views: 474
Size:  12.3 KB

    The Mongodb Plugin Options:
    Name:  mongodb_output_options.jpg
Views: 474
Size:  15.1 KB

    Thanks in advance.

  8. #8
    Join Date
    Apr 2017
    Posts
    5

    Default

    Quote Originally Posted by ervison View Post
    Thank for you reply.

    Do you have some example? I am newbie in Pentaho Keetle.

    My Document Path:
    Name:  mongdboutpup_schema.jpg
Views: 474
Size:  12.3 KB

    The Mongodb Plugin Options:
    Name:  mongodb_output_options.jpg
Views: 474
Size:  15.1 KB

    Thanks in advance.
    If anyone have this problem, the solution is to create 2 "MongoDB Output". In the first output, you need to set the array (the array will be recreated every time that the update query runs sucessfuly) . I did It using a dummy field.






    In the second "MongoDB Output", You need to execute a push to populate the array.



    In the "Output Options" tab, You have to set Update, Upsert and "Modifier Update"

  9. #9
    Join Date
    Dec 2017
    Posts
    10

    Default

    I am trying to include in my transformation the big data mongodb option output, but it prompt an error just at icon drop in panel: Unable to create step org/pentaho/mongo/NamedReadPreference Name:  mongoerror.jpg
Views: 488
Size:  19.9 KB
    How can I solve it?
    I am using pentaho 8 over ubuntu xenial 16.04
    best regards

  10. #10
    Join Date
    Dec 2017
    Posts
    10

    Default

    SOLVED I had issue with the mongo-db-plugin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.