locked
SQL-Query: How to include an attribute of a parent element? RRS feed

  • Question

  • Assume a container with documents following this structure:

    {
    "id": "some id",
    "attr1": "something",
    "attr2": "something",
    "children": [
    {
    "childattr1": "something",
    "childattr2": "something"
    },
    {
    "childattr1": "something",
    "childattr2": "something"
    },
    ]

    }

    Now, I want to receive all the children of every document in the container, but I want to include an attribute from the parent they reside in, so I want to have a result like this:

    [
    {
    "attr1": "something"
    "childattr1": "something",
    "childattr2": "something"
    },
    {
    "attr1": "something"
    "childattr1": "something",
    "childattr2": "something"
    },
    ]

    How would I write such a query?


    Sunday, December 8, 2019 1:40 PM

All replies

  • Hi Maxdev429,

    The query will look something like the following:

        SELECT {"childattr1":children.childattr1, "childattr2":children.childattr2} AS attr1
        FROM attr1 c
        WHERE c.attr1 = *

    There is an example that is close to your JSON example: Query the JSON items

        SELECT {"Name":f.id, "City":f.address.city} AS Family
        FROM Families f
        WHERE f.address.city = f.address.state

    Where the result set would look like:

        [{
            "Family": {
                "Name": "WakefieldFamily",
                "City": "NY"
            }
        }]

    Family being equal to attr1 and Name and City being childattr1 and childattr2. You would be searching on a wildcard versus searching on a specific value.

    I hope this helps. Do want to make you aware of a SQL Query Demo environment to help with queries.

    Regards,

    Mike


    Monday, December 9, 2019 11:14 PM
  • Hi Maxdev429,

    Want to follow up and see if you were able to get a resolution to your question. Please let me know if you have additional questions.

    Thank you,

    Mike

    Friday, December 13, 2019 5:49 PM