Elastic search DSL Syntax equivalence for SQL statement

Question

I'm trying to replicate the below query logic in an elastic search query but something's not right.

Basically the query below returns one doc. I'd like either the first condition to be applied: "name": "iphone" OR the more complex second one which is: (username = 'gogadget' AND status_type = '1' AND created_time between 4532564 AND 64323238). Note that the nested bool must inside the should would take care of the more complex condition. I should still see 1 doc if I change the outside match of "name": "iphone" to be changed to "name": "wrong value". But I get nothing when I do that. I'm not sure where this is wrong.

The SQL Query is here below.

SELECT * from data_points 
WHERE name = 'iphone'
    OR 
(username = 'gogadget' AND status_type = '1' AND created_time between 4532564 AND 64323238) 


{
    "size": 30,
    "query": {
        "bool": {
            "must": [
                {
                    "bool": {
                        "minimum_should_match": "1",
                        "should": [
                            {
                                "bool": {
                                    "must": [
                                        {
                                            "match": {
                                                "username": "gogadget"
                                            }
                                        },
                                        {
                                            "terms": {
                                                "status_type": [
                                                    "3",
                                                    "4"
                                                ]
                                            }
                                        },
                                        {
                                            "range": {
                                                "created_time": {
                                                    "gte": 20140712,
                                                    "lte": 1405134711
                                                }
                                            }
                                        }
                                    ]
                                }
                            }
                        ],
                        "must": [],
                        "must_not": []
                    }
                },
                {
                    "match": {
                        "name": "iphone"
                    }
                }
            ]
        }
    }
}

Answer 1

should query will match the query and return.

You don't need use must to aggregate your OR query.

The query should like:

{
    "query": {
        "bool": {
            "should": [{
                "bool": {
                    "must": [{
                        "match": {
                            "username": "gogadget"
                        }
                    }, {
                        "terms": {
                            "status_type": [
                                "3",
                                "4"
                            ]
                        }
                    }, {
                        "range": {
                            "created_time": {
                                "gte": 20140712,
                                "lte": 1405134711
                            }
                        }
                    }]
                }
            }, {
                "match": {
                    "name": "iphone"
                }
            }]
        }
    }
}