0

So I am trying to pivot some large Json arrays into a row by row format (postgres db). So imagine

{“searchResults”:[{“id”:“89705”,“score”:42,“title”:“Foo.”,“properties”:{…

Currently the most results we have in an array is about 300~ id's and explicitly saying; 

Data::Json->'searchResults'->0->'tags'->0->>'label' as "Tag - Result 1",
...
Data::Json->'searchResults'->300->'tags'->0->>'label' as "Tag - Result

1",

Ideal Output 

Array, ID , Score, Title
----
1 89705, 42, foo
1 89706, 34, bar
2 90003, 54, thing
2 98594, 53, that

(so 1,2 represent different rows in the initial table that both contain ??? amounts of objects the JSON data array)

Expanded JSON

    {
"searchResults": [
{
  "id": "897096",
  "score": 42,
  "title": "foo.",
  "properties": {
    "@type": "blah",
  },
  "publishedDate": "2018-06-30T10:20:20.555040Z",
  "comments": [
    {
      "content": "",
      "owner": {
        "firstName": "",
        "id": 0,
        "lastName": ""
      },
      "id": 0,
      "contentType": "",
      "documentPk": 0,
      "workflowStep": 0,
      "order": 0
    }
  ],
  "tags": [
    {
      "tag": 783,
      "label": "NO",
      "iconClass": "",
      "subGroup": "",
      "exclude": false
    },
    {
      "tag": 786,
      "label": "Different name",
      "iconClass": "",
      "subGroup": "",
      "exclude": false
    }
  ],
  "reviewTags": [
    {
      "tag": 2,
      "label": "Accept",
      "iconClass": "",
      "subGroup": "",
      "exclude": false
    }
  ],
  "original": {
    ..."names": [
      {
        "full_name": "This name"
      }
    ],
    "Entry Type": "Organization",
    "Last Updated": "2018/05/03",
    "Hit Category": "N/A",
    "Aliases": [
      "Olaj",
      "hbhbhb"
    ]
  },
  "snippet": "",
  "url": "",
  "source": "_"
},
{
  "id": "879057",
  "score": 36,
  "title": "name of company",
  "properties": {
    "@type": "",
    "category": "SOE",
    "type": "Organization",
    "country": "Korea, Republic Of",
    "subcategory": ""
  },
  "publishedDate": "2018-05-31T10:20:20.559714Z",
  "comments": [
    {
      "content": "",
      "owner": {
        "firstName": "",
        "id": 0,
        "lastName": ""
      },
      "id": 0,
      "contentType": "",
      "documentPk": 0,
      "workflowStep": 0,
      "order": 0
    }
  ],
  "tags": [
    {
      "tag": 783,
      "label": "NO",
      "iconClass": "",
      "subGroup": "",
      "exclude": false
    },
    {
      "tag": 786,
      "label": "Different name",
      "iconClass": "",
      "subGroup": "",
      "exclude": false
    }

Any advise on what my options are here ?

Thanks @a_horse_with_no_name this worked perfectly.

Timothy Goodman
  • 15
  • 6

1 Answers1

0

it's unclear to me how the JSON continues in the array, but it seems you are looking for something like this:

with testdata(props) as (
 values (
  '{"searchResults": 
     [ 
       {"id":"89705","score":42,"title":"Foo"},
       {"id":"89706","score":34, "title":"bar"}
     ]}'::jsonb
 ) 
)
select x.idx, 
       x.val ->> 'id' as id,
       x.val ->> 'score' as score, 
       x.val ->> 'title' as title
from testdata, jsonb_array_elements(props -> 'searchResults') with ordinality as x (val, idx);

returns

idx | id    | score | title
----+-------+-------+------
  1 | 89705 | 42    | Foo  
  2 | 89706 | 34    | bar
  • ignoring extra fields it just then repeats as another nested array {“searchResults”:[{“id”:“89705”,“score”:42,“title”:“Foo.”,“properties”:{…}}{"id:"... – Timothy Goodman Jun 20 '18 at 13:30
  • Thank you for the feedback, where you explain whats happening before the select ? – Timothy Goodman Jun 20 '18 at 13:33
  • That's a [common table expression](https://www.postgresql.org/docs/current/static/queries-with.html) to supply testdata (hence the name) –  Jun 20 '18 at 13:34
  • What do you mean with "it just repeats as another nested array"? Please [**edit**](https://stackoverflow.com/posts/50949021/edit) your question and provide a complete example of your (relevant) JSON structure. The sample you have shown only contains a single array, so there can't be another array that is repeated. –  Jun 20 '18 at 13:35
  • @TimothyGoodman: valid JSON is better, but anyway. Even with that, my query does what you seem to expect: http://rextester.com/GSBT6501 –  Jun 20 '18 at 13:49
  • Thank you for this, however I seem to be having some trouble converting this from the `With JSON..` to being from a 'DATA' Column of a table any advise would be appreciated. – Timothy Goodman Jun 21 '18 at 12:22
  • Simply remove everything before `select` and replace the table and column names with what you have –  Jun 21 '18 at 12:24