# Retrieve the original raw document via SQL

**URL:** <https://forum.opensearch.org/t/retrieve-the-original-raw-document-via-sql/14164>\
**Category:** SQL\
**Created:** [May 2, 2023, 3:25pm UTC](https://forum.opensearch.org/t/retrieve-the-original-raw-document-via-sql/14164 "2023-05-02T15:25:24Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![PeterS](https://avatars.discourse-cdn.com/v4/letter/p/bc8723/32.png) [@PeterS](https://forum.opensearch.org/u/PeterS)\
**Post date:** [May 2, 2023, 3:25pm UTC](https://forum.opensearch.org/t/retrieve-the-original-raw-document-via-sql/14164/1 "2023-05-02T15:25:25Z")

</div>

Is it possible to get the original raw document via SQL? E.g.:

```auto
SELECT _source FROM index WHERE indexedField = ...

```

Currently, I have to map arrays as “nested” type and use self-joins to access objects in arrays. With raw document I can get arrays from Json directly.

---

<div class="post-metadata">

**Author:** ![andrewc](https://avatars.discourse-cdn.com/v4/letter/a/e95f7d/32.png) [@andrewc](https://forum.opensearch.org/u/andrewc)\
**Post date:** [May 2, 2023, 4:43pm UTC](https://forum.opensearch.org/t/retrieve-the-original-raw-document-via-sql/14164/2 "2023-05-02T16:43:09Z")

</div>

Interesting idea.

You can make a request to add this feature, [here](https://github.com/opensearch-project/sql/issues/new?assignees=&labels=enhancement%2C+untriaged&template=feature_request.md&title=%5BFEATURE%5D)  
This would similar to how [other meta-fields from OpenSearch](https://github.com/opensearch-project/sql/pull/1456) work

Alternatively, you can consider using the `format=json`output format to get the raw output from OpenSearch.

---

<div class="post-metadata">

**Author:** ![andrewc](https://avatars.discourse-cdn.com/v4/letter/a/e95f7d/32.png) [@andrewc](https://forum.opensearch.org/u/andrewc)\
**Post date:** [May 2, 2023, 5:36pm UTC](https://forum.opensearch.org/t/retrieve-the-original-raw-document-via-sql/14164/3 "2023-05-02T17:36:09Z")

</div>

Also worth noting: `nested` objects often return in the `inner_hits` field when inner\_hits are requested (such as in the SELECT clause). So it may not be good enough to retrieve the `_source` values.

---

<div class="post-metadata">

**Author:** ![PeterS](https://avatars.discourse-cdn.com/v4/letter/p/bc8723/32.png) [@PeterS](https://forum.opensearch.org/u/PeterS)\
**Post date:** [May 5, 2023, 9:24am UTC](https://forum.opensearch.org/t/retrieve-the-original-raw-document-via-sql/14164/4 "2023-05-05T09:24:00Z")

</div>

Thank you for your hints.

`jdbc` format seems to return only 1st item from arrays whereas `json` returns whole arrays.

Interesting that I can get similar results also in `jdbc` when I wrap table name into brackets.

```auto
POST _plugins/_sql?format=jdbc
{
  "query": 
    """SELECT * FROM (employees_nested)
         WHERE projects.name LIKE "%sql%"
           AND projects.started_year = 1999 """
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/mauve_hedgehog/original/2X/3/33547ea01a5b12dcca2958411d3edd97ae2ea8c1.png) [@system](https://forum.opensearch.org/u/system)\
**Post date:** [July 4, 2023, 9:24am UTC](https://forum.opensearch.org/t/retrieve-the-original-raw-document-via-sql/14164/5 "2023-07-04T09:24:40Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.